CODEWITHSUNDEEP
excel
SAH
SAH

Reputation: 49

Extract characters after certain other characters Excel

I have a list of cells that looks more or less like this:

I'm trying to figure out how I can extract certain key elements from these strings. If we take the first entry as example, this is what I want to extract and display in another cell:

ARB d 1

I want all characters before ":", then the first character after ":" and the first number (or ONE character before the first "-").

I've played around with TRIM, LEFT/RIGHT and FIND - but can't figure out how to get only ONE character after/before a certain character. This is what I've tried, but dont know how to limit the output to only ONE character:

TRIM(LEFT(E5;FIND(":";E5)-1))

Thanks in advance for any pointers or tips :-)

Update, got it working:

I ended up using this code, which was tweaked after jiggle's suggestion.

=TRIM(LEFT(E2;FIND(":";E2)-1)) & RIGHT(MID(E2;FIND(":";E2);2);1) & RIGHT(MID(E2;FIND("p.";E2);3);1)

This gave me the following output: ARBd1

Now I just need to add some separators and im all good to go. Thanks for all the help.

Upvotes: 2

Views: 65562

Answers (1)

John Lucas
John Lucas

Reputation: 1617

Find the first part : =LEFT(E5,FIND(":",E5)-1)

Find the second part: =MID(E5,FIND(":",E5)+1,1)

= MID ( Text , Start_num , Num_chars )

so here we're starting at the character after the ":", by adding 1 to the FIND

and taking 1 character, at the end

Find the last part: =MID(E5,FIND("-",E5)-1,1)

doing a similar thing to the second part, but now starting at the character before the "-" by subtracting 1 from where we found it, and again taking just 1 character

To combine them all together, just use the concatenation character "&" and space:

=LEFT(E5,FIND(":",E5)-1)&" "&MID(E5,FIND(":",E5)+1,1) & " " & MID(E5,FIND("-",E5)-1,1)

EDITED to replace commas with semi colons (for different cultures):

=LEFT(E5;FIND(":";E5)-1)&" "&MID(E5;FIND(":";E5)+1;1) & " " & MID(E5;FIND("-";E5)-1;1)

Upvotes: 5

Related Questions