2

I have these two cells:

35hz 24.5/jy;8243-9884;100093 and 
aslhz/45.77;94-224444;103944234

I'm trying to isolate the number in between the semi-colons. I've used the function:

=RIGHT(A1, FIND(";",A1)-1)

but it removes more than just what's next to the semi-colon. Is there something wrong with the formula?

3

With data in A1, in B1 enter:

=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),2*999-998,999))

enter image description here

EDIT#1:

Make the 2 into a 1 to get everything before the first ;.
Make the 2 into a 3 to get everything after the second ;.

2
  • That's thinking outside the box, I'll give you that...! – Jonno Jun 10 '16 at 2:11
  • @Jonno See my EDIT#1 – Gary's Student Jun 10 '16 at 2:23
2

RIGHT(CELL,LEN) gives you LEN characters from the right, whereas FIND returns the first semicolon.

You need something like this:

A1 35hz 24.5/jy;8243-9884;100093
B1 =FIND(";",A1)
C1 =RIGHT(A1,LEN(A1)-B1)
D1 =FIND(";",C1)
E1 =LEFT(C1,D1-1)

Once you see how this works, you can combine the formula into a single cell.

You should see the value 8243-9884 in E1.

2

Use Excel Flash Fill (2013 and later): With the text in a contiguous column, type out the first desired result, then start typing the second desired result and see Flash Fill suggest the outcome.

Look Ma, no formulas!

enter image description here

Edit:

Flash Fill also lets you correct flaws in the logic it detected. In the screenshot below, row 6 and 7 initially take the last word of column C. After correcting row 6 to the second last word, row 7 automatically adjusts to that new rule and shows "is" instead of "good".

enter image description here

5
  • Great idea! It's there a way to apply it to several cells (100+)? – Máté Juhász Jun 10 '16 at 6:18
  • @MátéJuhász Of course. Flash Fill starts working when it recognizes a pattern in the data and it works best when the data has some consistency. In the above example, it will recognize that the text between the two semicolons is wanted as the result. In more complex scenarios, Flash Fill suggests, and you can edit to correct the result, which it will then apply to similar results. It's an amazing algorithm. For one-time clean up jobs, Flash Fill will often be more efficient than Left/Right/Mid/Find/Substitute formulas. – teylyn Jun 10 '16 at 7:37
  • @MátéJuhász see my edit for an example involving different columns and correcting the output. – teylyn Jun 10 '16 at 7:55
  • It's worth noting that this won't update if the values change in the original cells, where a formula cell will change if the original values are modified. – Jonno Jun 10 '16 at 8:06
  • That's right. Flash Fill is great for one-time transformations. For repeatable scenarios use formulas, or better yet, Power Query. – teylyn Jun 11 '16 at 5:53
1

You can do it like this to get the middle number.

=MID(A1,(FIND(";",A1)+1),(FIND(";",A1,(FIND(";",A1)+1))-(FIND(";",A1)+1)))

MID gives you a string starting at a given character, and of specified length. We find the first ";" by using FIND and use it for the start.

Now we need the length, and for that we have to find the second ";" as well, and we do it again by FIND, but starting at the position after the first ";". This part gives us position of the 2nd ";":

FIND(";",A1,(FIND(";",A1)+1))

From that, we substract the position of the 1st ";" to get the length and combine everything in the formula above. Note that if your cell doesn't have at least 2 semicolons, the formula will return an error.

If you need the last number, you need the 2nd ";", so you could use:

=RIGHT(A1,LEN(A1)-FIND(";",A1,(FIND(";",A1)+1)))

To get the first number is the simplest:

=LEFT(A1,FIND(";",A1)-1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.