CODEWITHSUNDEEP
excelexcel-formula
spak
spak

Reputation: 253

Excel to find a specific substring within a cell

I am trying to extract a string from a cell and I am running into some trouble. In this example I am looking to extract the string ARM52CVA from

A18031600473 ( FLORENCE - ARM52CVA )

Ive tried this formula and it doesnt seem to work for me =RIGHT(C3,SEARCH("-",C3))

Upvotes: 1

Views: 53

Answers (3)

QHarr
QHarr

Reputation: 84465

The methods above are far more reliable but if the white space is consistent you can also use

=MID(A1,FIND("-",A1,1)+2,(FIND(")",A1,1)-3-FIND("-",A1,1)+1))

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60389

You would have come closer with either of:

=RIGHT(C3,LEN(C3)- SEARCH("-",C3))
=MID(C3,FIND("-",C3)+2,99)

but they would have left the ending.

If your data is exactly as you show it, with all of the data and spaces as shown, then try:

=INDEX(TRIM(MID(SUBSTITUTE(C3," ",REPT(" ",99)),{1,99,198,297,396},99)),5)

If there is more variability, you'll need to show more data.

enter image description here

Upvotes: 1

user4039065
user4039065

Reputation:

Try,

=REPLACE(REPLACE(A1, 1, FIND("- ", A1)+1, ""), FIND(" ",  REPLACE(A1, 1, FIND("- ", A1)+1, "")), LEN(A1), "")

enter image description here

Upvotes: 0

Related Questions