How to find values in string? - Urgent!

  • Thread starter Thread starter staeri
  • Start date Start date
S

staeri

I have a cell containing for example:

Cell A1: -7 / +18 (%)
Cell A2: -4.5 / +3.5 (pts)

I want to break out the two values into separate cells, for example:
Cell A2 = -7, Cell B2 = 18
Cell A3 = -4.5, Cell B3 = 3.5

Can someone please tell me how?

Regards,

S
 
Try this:

1. Highlight cell A1
2. Go to Data -> Text to Columns
3. Select Delimited then Next
4. Select Space as the Delimiter then Next
5. Finish

See if that works.
 
Use these formulas in B1 and C1, copied down.........

=LEFT(A1,FIND("/",A1,1)-2)
=MID(A1,FIND("/",A1,1)+2,99)

Vaya con Dios,
Chuck, CABGx3
 
I have a cell containing for example:

Cell A1: -7 / +18 (%)
Cell A2: -4.5 / +3.5 (pts)

I want to break out the two values into separate cells, for example:
Cell A2 = -7, Cell B2 = 18
Cell A3 = -4.5, Cell B3 = 3.5

Can someone please tell me how?

Regards,

S
Click to expand...

Here's one way:

Download and install Longre's free morefunc.xll add-in from


Then use the Regular Expression Formulas:

=REGEX.MID(A1,"[-+]?\d+(\.\d*)?",1) for the first number and
=REGEX.MID(A1,"\d+(\.\d*)?",2) for the second number

These formulas make the assumptions:
1. The FIRST digit in the string is always part of the first value of
interest.
2. All values have a digit before the decimal place. (e.g. 0.007 is
OK but .007 would not be a valid entry).
3. The preceding "+" or "-" signs are optional, but are included in
the extraction for the first value, only (as you show above). It is not
included with the second value.

If any of those assumptions are not correct, the Regular Expression can be
easily modified.


--ron
 
Your second formula also extracts the terminal textual part of the string. The
OP only requested the numeric portion.

--ron

Use these formulas in B1 and C1, copied down.........

=LEFT(A1,FIND("/",A1,1)-2)
=MID(A1,FIND("/",A1,1)+2,99)

Vaya con Dios,
Chuck, CABGx3
Click to expand...

--ron
 
I have a cell containing for example:

Cell A1: -7 / +18 (%)
Cell A2: -4.5 / +3.5 (pts)

I want to break out the two values into separate cells, for example:
Cell A2 = -7, Cell B2 = 18
Cell A3 = -4.5, Cell B3 = 3.5

Can someone please tell me how?

Regards,

S
Click to expand...

Here's one way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/


Then use the Regular Expression Formulas:

=REGEX.MID(A1,"[-+]?\d+(\.\d*)?",1) for the first number and
=REGEX.MID(A1,"\d+(\.\d*)?",2) for the second number

These formulas make the assumptions:
1. The FIRST digit in the string is always part of the first value of
interest.
2. All values have a digit before the decimal place. (e.g. 0.007 is
OK but .007 would not be a valid entry).
3. The preceding "+" or "-" signs are optional, but are included in
the extraction for the first value, only (as you show above). It is not
included with the second value.

If any of those assumptions are not correct, the Regular Expression can be
easily modified.


--ron
 
You're right Ron, my bad.........will have to put it on my list that I must
learn to read someday <G>

Thanks for catching it.

Vaya con Dios,
Chuck, CABGx3
 
Back
Top