CODEWITHSUNDEEP
excelexcel-formula
BIReportGuy
BIReportGuy

Reputation: 817

Extract last characters of a string in Excel

I have the following set of data. I need to extract the single character of 'C' into another column. It's not always going to be the letter 'C'.

I also need to extract the numbers 105, 120, 8 and etc into a separate column. Here's what I tried, but not sure how to figure this with multiple spaces in the string. =RIGHT(A12,LEN(A12)-FIND(" ",A12)) This formula only gives me 01202017 C 105.

AAPL 01202017 C 105
AAPL 01202017 C 120
ABX 01202017 C 8
AMD 01202017 C 2.5
CLVS 01202017 C 40

Thanks,

Upvotes: 0

Views: 1198

Answers (2)

Slai
Slai

Reputation: 22866

= Mid(A1, Search(" ? ", A1) + 1, 1) for the letter and

= Mid(A1, Search(" ? ", A1) + 3, 99) for the number.

Search is like Find but it also supports wildcard symbols where ? matches any character, and * matches any 0 or more characters.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

Use Text to columns would do it quickly, but if you want a formula then use this:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",999)),COLUMN(B:B)*999,999))

copy it over one column and down the dataset.

enter image description here

Upvotes: 4

Related Questions