| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Converting string to number
hi,
I have a set of data that is to be cut and pasted from an external source. A typical cell reads '32813.58 sq mm', without the speech marks. The cell could be formatted before the pasting; but how do I strip off the units to leave the number only? The number could be any size. thanks |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Converting string to number
=LEFT(A1, LEN(A1)-5)
Will return your data as required as long as the text to be stripped is constant -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "kenppy" wrote: hi, I have a set of data that is to be cut and pasted from an external source. A typical cell reads '32813.58 sq mm', without the speech marks. The cell could be formatted before the pasting; but how do I strip off the units to leave the number only? The number could be any size. thanks . |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Converting string to number
Another alternative if you are pasting text into Excel is to use the text to
columns facility. You'll find this under data - text to columns. Ask again if you need more help on this. -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "kenppy" wrote: hi, I have a set of data that is to be cut and pasted from an external source. A typical cell reads '32813.58 sq mm', without the speech marks. The cell could be formatted before the pasting; but how do I strip off the units to leave the number only? The number could be any size. thanks . |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Converting string to number
This works but is still a string, how do I turn this to number? thanks says... =LEFT(A1, LEN(A1)-5) Will return your data as required as long as the text to be stripped is constant |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Converting string to number
The data is already loading into columns but the length of the stuff to
strip may not be predicted, how would this be treated? thanks says... Another alternative if you are pasting text into Excel is to use the text to columns facility. You'll find this under data - text to columns. Ask again if you need more help on this. |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Converting string to number
=--LEFT(A1, LEN(A1)-5) -- HTH Bob "kenppy" wrote in message om... This works but is still a string, how do I turn this to number? thanks says... =LEFT(A1, LEN(A1)-5) Will return your data as required as long as the text to be stripped is constant |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Converting string to number
=VALUE(LEFT(A1, LEN(A1)-5))
-- Best Regards, Luke M "kenppy" wrote in message om... This works but is still a string, how do I turn this to number? thanks says... =LEFT(A1, LEN(A1)-5) Will return your data as required as long as the text to be stripped is constant |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Converting string to number
Under Data - Text to Columns, choose delimited. Then choose space as your
delimiter. -- Best Regards, Luke M "kenppy" wrote in message om... The data is already loading into columns but the length of the stuff to strip may not be predicted, how would this be treated? thanks says... Another alternative if you are pasting text into Excel is to use the text to columns facility. You'll find this under data - text to columns. Ask again if you need more help on this. |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Converting string to number
Sorry try
=LEFT(A1, LEN(A1)-6) -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "kenppy" wrote: This works but is still a string, how do I turn this to number? thanks says... =LEFT(A1, LEN(A1)-5) Will return your data as required as long as the text to be stripped is constant . |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Converting string to number
Thanks all of you, it's working now
Sorry try =LEFT(A1, LEN(A1)-6) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting a text word or text string to a number | Excel Discussion (Misc queries) | |||
| Converting a string into a number format | Excel Discussion (Misc queries) | |||
| Converting string of letters to Number | Excel Discussion (Misc queries) | |||
| Extracting a number from a string and converting it into an Intege | Excel Worksheet Functions | |||
| converting number string to number with decimal | Excel Worksheet Functions | |||
Linear Mode
