Extracting specific data from a single cell

[Richard Layzell]

I need to extract numerical data from a cell that contains
a numerical and text data mix.

Example:
Cell A1 contains: 1234.00eur

I want to seperate the 'eur' so that I am left with 1234.00

I know about the 'text to colums' function and this does
work- but only for the cells that have an equal number of
digits. i.e. '1234.00eur' splits to '1234.00' and 'eur'
however 123456.00eur splits to '123456.' and '00eur'.

Can anyone help me? I just need to know how to split or
ignore the last 3 letters of a list of cells.

Many thanks in advance,

Richard

 

[Naz]

If cell A1 has the value 1234.00eur then the following
formula will work

=MID(A1,1,FIND("e",A1)-1)

it extracts everything before the e, so should not be a
problem with 123456.00eur

 

[JMay]

=IF(MOD(LEN(A5),2)=0,MID(A5,1,LEN(A5)-3),LEFT(A5,LEN(A5)-3))
The above will produce the numeric portion to a cell still as text; to
convert
to a number in a blank cell (somewhere) enter 1, then copy it (the 1) and
then Highlight the formularized cell and select Edit, Paste-Special, select
both Values and Multiply, OK,OK
Now your cell is a number
HTH

 

[Jeff Meyers]

I am not really sure what JMay is going for with that formula. The true and
false options of the IF formula (MID(A5,1... and Left(A5...) will always
return the same results because they essentially same the same thing (start
at the left edge of A5; Mid(A5,1 (the 1 here says to start with with first
character)).

If your cells always contain three alpha values after the number, then all
you will need is =LEFT(A5,LEN(A5)-3). This still returns a text value as
JMay stated, but you can modify the forumla for this =VALUE(formula). Then
make sure to format the cells to the number of decimals you need.

If you have more or less than three alpha characters in your cells, you will
need something different than above.

Jeff

 

[RagDyer]

Hey Jeff,

With so much talk in the group about unary being so efficient, all your
formula needs is:

=--LEFT(A5,LEN(A5)-3)


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


I am not really sure what JMay is going for with that formula. The true and
false options of the IF formula (MID(A5,1... and Left(A5...) will always
return the same results because they essentially same the same thing (start
at the left edge of A5; Mid(A5,1 (the 1 here says to start with with first
character)).

If your cells always contain three alpha values after the number, then all
you will need is =LEFT(A5,LEN(A5)-3). This still returns a text value as
JMay stated, but you can modify the forumla for this =VALUE(formula). Then
make sure to format the cells to the number of decimals you need.

If you have more or less than three alpha characters in your cells, you will
need something different than above.

Jeff

 

[Jeff Meyers]

RD,
I was not aware of the "--" to replace the VALUE formula.
Thanks for that one.
Do you know how that works?

 

[RagDyer]

I wasn't kidding when I said "All The Talk In The Group".
Check these Google links.

http://tinyurl.com/qvyp
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------



RD,
I was not aware of the "--" to replace the VALUE formula.
Thanks for that one.
Do you know how that works?