Parse a text field in Excel
I have a text field in excel that contains a lot of comments. Within those comments is a 6 digit number that always begins with the number 3. It's not always in the same location in the text but I need to parse out this number to another cell. How would I go about setting a formula to do this?
Hello,
if there is no other "3" before the 6 digits in the string, you could use
=MID(A1,FIND("3",A1),6)
cheers, teylyn
if there is no other "3" before the 6 digits in the string, you could use
=MID(A1,FIND("3",A1),6)
cheers, teylyn
ASKER
It looks like that would work but there's one problem. There are other numbers in the text but they don't begin with 3. So it's picking up any 3 no matter where it is in the field. For example:
887553 astm - Not what I want but it's in the field
330229 - What I want
So when I try that formula on this field it gives me:
3 astm
How can I make it just find any 6 digits together that only begin with 3?
887553 astm - Not what I want but it's in the field
330229 - What I want
So when I try that formula on this field it gives me:
3 astm
How can I make it just find any 6 digits together that only begin with 3?
Are there maybe any specific characters in front of the string you want? Like: Is there always a space character in front?
If so, you could use
=MID(A1,FIND(" 3",A1)+1,6)
cheers, teylyn
If so, you could use
=MID(A1,FIND(" 3",A1)+1,6)
cheers, teylyn
ASKER
Well there's supposed to be a space before the 3 but since there are so many hands in the cookie jar it's hard to keep the syntax correct when people enter the info. So to answer your question, there might be a space but then there might not be. Is there a way to locate a 6 digit number and then if it begins with a 3 give me that as the result?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
My approach above yields the following for these sample data:Input Result887553 astm ddd345678dd ddd 345678 fff 345678ddd.311223.ddd 311223AFIU 456789 123456 398765 ff 398765The \b indicates a word boundary, and explains why the second example returns nothing.The pattern can be refined if needed.
lsalvucci,Glad to help! If you have not already done so, I would really appreciate it if you could please return to my article https://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.htmland click 'Yes' for the 'Was this helpful?' voting.Patrick
ASKER
No problem. Truly amazing work with that article. You've got my vote!
=mid(a1,find(3,a1),6)
Thomas