CODEWITHSUNDEEP
excel
joon25
joon25

Reputation: 41

Excel extract text from alphanumeric in last filled cell in a row

Hi this request may be very specific but I have a row of cells that are filled in alphanumeric such as

enter image description here

Lets say column 1 is A with the row names. I want column 2 to show the latest cell filled to the right (for row 2 it would be PL0.25)

I also want the number to be removed, so only PL is written. Is there a formula that can do all of that in one line?

Upvotes: 1

Views: 112

Answers (2)

QHarr
QHarr

Reputation: 84465

Sounds like you always want the Left two characters then as below. This could go in A1 and then you drag down rows.

=LEFT(LOOKUP(2,1/(1:1<>""),1:1),2)

To be sure of last with Text only and to handle strings less than two in length try:

=IFERROR(LEFT(INDEX(1:1,,MATCH("zzz",1:1)),2),"")

Upvotes: 2

PaichengWu
PaichengWu

Reputation: 2689

write =LEFT(INDEX(2:2,MAX(IF($D2:$ZZ2<>"",COLUMN($D2:$ZZ2),""))),LEN(INDEX(2:2,MAX(IF($D2:$ZZ2<>"",COLUMN($D2:$ZZ2),""))))-4) in B2 then press ctrl + shift + enter

Upvotes: 1

Related Questions