# Remove everything to right and left of semicolon

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?

With data in A1, in B1 enter:

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

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 ;.

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

`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.

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!

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".

• Great idea! It's there a way to apply it to several cells (100+)? Jun 10, 2016 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. Jun 10, 2016 at 7:37
• @MátéJuhász see my edit for an example involving different columns and correcting the output. Jun 10, 2016 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. Jun 10, 2016 at 8:06
• That's right. Flash Fill is great for one-time transformations. For repeatable scenarios use formulas, or better yet, Power Query. Jun 11, 2016 at 5:53

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)
``````