Link to home
Create AccountLog in
Avatar of anthonytr
anthonytrFlag for United Kingdom of Great Britain and Northern Ireland

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,LEN(A1))

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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

yes
=TRIM(MID(A1,FIND("ABC",A1)+3,LEN(A1)))

gowflow
Avatar of anthonytr

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.
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
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
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
ok try this
=MID(A1,FIND("ABC",A1),SEARCH(" ",A1,FIND("ABC",A1)+4)-FIND("ABC",A1)-1)

gowflow
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account