Forum Discussion
extracting car plate from cell
I have to extract car plate from bank reference column. there are more than 10000 lines per month. The position of the car plate in the string varies. The car plate has min of 5 and max 10 alphabet+ ...
Data Starts from the B3
=IF(LEN(B3)>100,TEXTBEFORE(TEXTAFTER(B3," ",-2,0,0)," ",-1,0,0),TEXTBEFORE(TEXTAFTER(B3,"/",2,0,0),"/",1))
=IF(LEN(B3)>100,TEXTBEFORE(TEXTAFTER(B3," ",-2,0,0)," ",-1,0,0),TEXTBEFORE(TEXTAFTER(B3,"/",2,0,0),"/",1))
- this works!
I had my colleague change the formula to use it vertically because my data is all in column A.
The only missing part is if the car plate is the first word, the formula does not detect it.
This is the formula i used:
=IF(LEN(A6)>100,TEXTBEFORE(TEXTAFTER(A6," ",-2,0,0)," ",-1,0,0),TEXTBEFORE(TEXTAFTER(A6,"/",2,0,0),"/",1))
How to adjust this formula to detect the carplate that is at the start of the string?
