extract string

  • Thread starter Thread starter vera.krause
  • Start date Start date
V

vera.krause

Hi guys,

I've been trying to find out how to extract a string from
I got an area specification e.g."10.00X20.00" that I wanna split in
two cells.
One cell should reflect the string before the X w/o the decimal
places, in this case 10.
The other cell should reflect the sting after the X, in this case 20.

Apart from this, is there a chance extract + round up or down if there
were decimal places like "10.50X20.25"?

Any ideas how to do that?

Thanks,
Vera
 
With
A1: (a spec....eg 10.50X20.25)

Try something like this:

B1: =ROUND(LEFT(A1,SEARCH("X",A1)-1),0)
C1: =ROUND(MID(A1,SEARCH("X",A1)+1,255),0)

Using the example:
A1: 10.50X20.25
B1 returns 11
C1 returns 20

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
Hi guys,

I've been trying to find out how to extract a string from
I got an area specification e.g."10.00X20.00" that I wanna split in
two cells.
One cell should reflect the string before the X w/o the decimal
places, in this case 10.
The other cell should reflect the sting after the X, in this case 20.

Apart from this, is there a chance extract + round up or down if there
were decimal places like "10.50X20.25"?

Any ideas how to do that?

Thanks,
Vera
Click to expand...

A1: 10.50X20.25

B1: =LEFT(A1,FIND("X",A1)-1)
C1: =MID(A1,FIND("X",A1)+1,255)

To Round, merely embed the above in your desired
ROUND, ROUNDUP, ROUNDDOWN or whatever function. E.G. to round to zero decimal
places:

=ROUND(LEFT(A1,FIND("X",A1)-1),0)

Check HELP for the different ROUND functions (also CEILING, FLOOR) if you want
other than "ordinary" rounding (i.e. round up at the half; round down
otherwise).
--ron
 
Hi Vera,

With your string in A1, you can try these formula

in B1: =ROUND(LEFT(A1,SEARCH("x",A1)-1),0)
in C1: =ROUND(RIGHT(A1,LEN(A1)-SEARCH("x",A1)),0)

So, if you have "10.25x20.56" in A1 (without the quotes), you will get
10 in B1 and 21 in C1. If you have other values down column A, then
just copy the formulae down to suit.

Notice that I've used SEARCH rather than FIND, so it doesn't matter if
you have 10.25x20.56 or 10.25X20.56

Hope this helps.

Pete
 
Back
Top