asked on Excel formula to pull specific data from a string
Hi,
I have the following formula which looks for a specific string and then pulls data found to the right of the found string:
=MID(A1,FIND("ABC",A1)+3,L
The number which follows ABC is 6 numbers long and may have a space or not (ABC 000000 or ABC000000) .
Is it possible to to only return the 6 or 7 numbers after the ABC prefix and nothing else?
Thanks
I have the following formula which looks for a specific string and then pulls data found to the right of the found string:
=MID(A1,FIND("ABC",A1)+3,L
The number which follows ABC is 6 numbers long and may have a space or not (ABC 000000 or ABC000000) .
Is it possible to to only return the 6 or 7 numbers after the ABC prefix and nothing else?
Thanks
Microsoft ExcelSpreadsheets
Last Comment
Rgonzo1971
ASKER
Hi gowflow,
I still can't trim the characters after the ABC 000000 numbers. See below:
1788 / 33000 / MCS 170315 PC NQ / ABC 327996
Mr John Smith
An Address
Another Address
Somewhere
Earth
I just want to pull the ABC 327996 out.
I still can't trim the characters after the ABC 000000 numbers. See below:
1788 / 33000 / MCS 170315 PC NQ / ABC 327996
Mr John Smith
An Address
Another Address
Somewhere
Earth
I just want to pull the ABC 327996 out.
sorry can you post a sample workbook its not clear as there are carriage returns and is what you posted all in 1 cell ?
gowflow
gowflow
Let me rephrase:
You have all this in A1
1788 / 33000 / MCS 170315 PC NQ / ABC 327996
Mr John Smith
An Address
Another Address
Somewhere
Earth
and need to get this:
Mr John Smith
An Address
Another Address
Somewhere
Earth
Is that what you want ?
gowflow
You have all this in A1
1788 / 33000 / MCS 170315 PC NQ / ABC 327996
Mr John Smith
An Address
Another Address
Somewhere
Earth
and need to get this:
Mr John Smith
An Address
Another Address
Somewhere
Earth
Is that what you want ?
gowflow
ASKER
Sort of,
I have all this in A1:
1788 / 33000 / MCS 170315 PC NQ / ABC 327996
Mr John Smith
An Address
Another Address
Somewhere
Earth
and need to get this:
ABC 327996
I have all this in A1:
1788 / 33000 / MCS 170315 PC NQ / ABC 327996
Mr John Smith
An Address
Another Address
Somewhere
Earth
and need to get this:
ABC 327996
ok try this
=MID(A1,FIND("ABC",A1),SEA
gowflow
=MID(A1,FIND("ABC",A1),SEA
gowflow
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
=TRIM(MID(A1,FIND("ABC",A1
gowflow