Let's say that the cells below are A1 and A2. I want to extract text in the following format: ????-??-???-???? and place it in the cell immediately to the right (B1 and B2). In this case, the text that should be extracted would be 4210-01-208-4616 (B1) and 4820-01-208-4595 (B2). Is there a formula to do this? Much thanks in advance.
99657 4860375 4210-01-208-4616
99657 283281-0002 4820-01-208-4595 326 13
Hi, welcome to the forum
Try this, copied down...
=MID(A1,FIND("-",A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=3,1,FIND("-",A1,1)+1))-4,16)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for the welcome. Unfortunately, that formula did not work. It simply returned values of "0" in Column B.
It worked for me?
99657 4860375 4210-01-208-4616 4210-01-208-4616 99657 283281-0002 4820-01-208-4595 326 13 4820-01-208-4595
B1=MID(A1,FIND("-",A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=3,1,FIND("-",A1,1)+1))-4,16)
copied down
That's odd. Let me try it again.
Here are two more formulas to do that.
=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,16)
=MID(A1,SEARCH("????-??-???-????",A1),16)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
AlKey, thanks for this 1...
=MID(A1,SEARCH("????-??-???-????",A1),16)
I tried that too, but used FIND instead of SEARCH...didnt work, so I brute-forced it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks