Re: extract string

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Thu, 12 Jul 2007 05:39:44 -0700, vera.krause@xxxxxxxxxxxxxx wrote:

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

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
.

Relevant Pages

  • Re: Single cell miscalculation
    ... it, but when I press ENTER to apply it, it changes the uppercase ROUND ... formula applied to it, the result is still displaying as £18,095.84 ... including zeros, at its position. ... So now your current cell will ...
    (comp.sys.acorn.apps)
  • Re: Round Len Function Help
    ... multiple cells referencing the dp in a key cell. ... its the number in a cell that I need to round either up or down ... Aaron, Slash, Slashman, Waikato300ZXclub or whoever you prefer. ... or multiples of the number in Q16. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Negative time displayed as #######
    ... and to round the result with 10 minutes. ... "Round" overtime according to following conventions: round to h:00 if overtime is less than h:20; round to h:30 if overtime is at least h:20 and ... It would be easier if we calculate unrounded overtime in a cell, say C1, then calculate the "rounded" overtime in D1. ... other a TEXT formula like one of those above, which you can use for display ...
    (microsoft.public.excel.misc)
  • Re: concatenate formatted cells in excel
    ... I have excel round the numbers to no decimal places and I ... pull the numbers exactly as displayed into a single cell like this A1/B1/C1 ... but you would have to include the conditional formatting ... Dim str As String ...
    (microsoft.public.excel.programming)
  • Re: OT-Pro-Immigration Rallies Are Held Across Country
    ... until you started braying about how good they were at their "round ups", ... you opened their cell doors, and told them to come out..... ... It's the same all along the border. ... They find a lot of dead ones in the desert south of Ajo every year. ...
    (rec.outdoors.rv-travel)