convert text format to number

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Jun 15, 2005

• #2

If 123.100grams is in A1,

Try:

B1 =LEFT(A1,MIN(IF(ISNUMBER(SEARCH({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A1)),SEARCH({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A1)))-1)

B1 should return 123.100

C1 =LEN(B1)-FIND(".",B1)

C1 should return 3, for 3 decimal places.

 

Upvote 0

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Jun 15, 2005

• #3

Mark W. said:

{=LEFT(C4,MATCH("a",MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1)))}

Click to expand...

ray: Nice.

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Jun 15, 2005

• #4

{=LEFT(A1,LEN(A1)-MATCH(TRUE,ISNUMBER(MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0),0)+1)}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, {}, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on this topic see the Excel help topic for "Create an array formula".

EDIT: changed cell reference to A1

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Jun 15, 2005

• #5

Oaktree said:

Mark W. said:

{=LEFT(C4,MATCH("a",MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1)))}

Click to expand...

ray: Nice.

Click to expand...

But, flawed. Still evaluating revision posted above. Seems okay.

 

Upvote 0

PA HS Teacher

Well-known Member

Joined

Jul 17, 2004

Messages

2,838

• Jun 15, 2005

• #6

Here's 2 cent's worth:

LEFT(A1,MATCH(0,--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),".0123456789")),0)-1)
array entered with control + shift + enter

[edit]
Another variation of my first approach
=LEFT(A1,MATCH(0,--ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)&0)),0)-1)

My First approach did not work for negative numbers, though my second does. (Mark and Barry's also seem to work for negative numbers)

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Jun 15, 2005

• #7

how about

=LEFT(A1,MATCH(10,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

also an array formula

 

Upvote 0

PA HS Teacher

Well-known Member

Joined

Jul 17, 2004

Messages

2,838

• Jun 15, 2005

• #8

Looks like Barry wins.

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Jun 15, 2005

• #9

Yeah, Barry's solution demonstrates a good working knowledge of Excel's sort order.

 

Upvote 0

M

mwb

Board Regular

Joined

Sep 3, 2004

Messages

88

• Jun 16, 2005

• #10

Thank you all!
I have both Barry's and Mark's formulas working and with Oaktree's len formula i have the result that I need.

Question though; I would like to understand the difference between the following:

"...match("a",MID(..." vs "...match(10,--MID(..." I assume the latter is looking for a number not a character, but what does the "--" do? What does the 10 represent?

Thanks again,
Mike

 

Upvote 0