Need to extract numbers from a cell and convert to number values. Please help.

[Eclipsemints]

Hi all,

I had talked to my brother who knows a bit about programming but not enough about Excel.

I think it would be easy to start with an example to show what i'd like to do.

A B C
1 12.00 NZD 12.00 NZD
2 15.00 NZD 15.00 NZD
3 5.00 NZD 5.00 NZD


I want to be able to just extract the numbers from column A and put them in B and put the text in column C.

From my understanding, I can use a combination of LEFT and LEN for B. But my problem is that the numbers, are still text values. Is there a way to extract the numbers and convert them into number values so that I can sum them?

Any help would be much appreciated.

Thanks

[Speshul]

For the numbers;
=LEFT(A1,FIND(" ",A1,1)-1)
If it's always 3 letters, you can use
=RIGHT(A1,3)

If the letters are variable length, as long as there are no spaces use:
=MID(A1,FIND(" ",A1,1)+1,LEN(A1))

[Eclipsemints]

For the numbers;
=LEFT(A1,FIND(" ",A1,1)-1)
If it's always 3 letters, you can use
=RIGHT(A1,3)

If the letters are variable length, as long as there are no spaces use:
=MID(A1,FIND(" ",A1,1)+1,LEN(A1))

just add 0 to the results,
=LEFT(A1,FIND(" ",A1,1)-1)+0
other ways are
=--LEFT(A1,FIND(" ",A1,1)-1)
=LEFT(A1,FIND(" ",A1,1)-1)*1

you guys are legends! thanks so much!

[martindwilson]

just add 0 to the results,
=LEFT(A1,FIND(" ",A1,1)-1)+0
other ways are
=--LEFT(A1,FIND(" ",A1,1)-1)
=LEFT(A1,FIND(" ",A1,1)-1)*1