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+ numbers in it. What formula can I use for this?
11 Replies
- 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)) - FILTERXML(), TEXTSPLIT(), TEXTAFTER(), TEXTBEFORE() all these function may help you. Please post few sample data and show your desired output so that we can put formula for you.
- Jun 25 2024 01:01 AM
- WHK 88966W8011VB/EE/88966W8011VB/PD/ S1464157B TRUSTY TO WONG HEA KONG EXCESS REFUND SGD 1,000.00 SMJ7093D JTEGD56M007150803
- SEOW KENG SENG /EE/SLF2195J/PD/OTHR /IREF/20240617DBSSSGSGBRT5155443//PAYNOW Others
Car Plate: SMJ7093D, SLF2195J
Above are two sample. Most of the references have the similar pattern.
Can you provide more specific information on how the car plate and the rest of values/strings are seen on these cells. Maybe an example would be useful!
Understanding the specific rules that the strings on these cells follow will help us help you in the most efficient way.
For instance, you could instruct that the car plate is always preceded by "X" sequence/number of values. Or, that the car plate is always at the end of the string. Or, that the car plate is always between two specific numbers... There can be many rules, but make sure they always apply (to all 10000 lines).
- - WHK 88966W8011VB/EE/88966W8011VB/PD/ S1464157B TRUSTY TO WONG HEA KONG EXCESS REFUND SGD 1,000.00 SMJ7093D JTEGD56M007150803
- SEOW KENG SENG /EE/SLF2195J/PD/OTHR /IREF/20240617DBSSSGSGBRT5155443//PAYNOW Others
Car Plate: SMJ7093D, SLF2195J
Above are two sample. Most of the references have the similar pattern.
