|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 5th, 2010, 01:48 PM
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
March 5th, 2010, 03:36 PM
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
March 5th, 2010, 03:39 PM
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
March 5th, 2010, 03:49 PM
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
March 5th, 2010, 03:55 PM
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
March 5th, 2010, 04:25 PM
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
March 5th, 2010, 04:26 PM
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
March 5th, 2010, 04:27 PM
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
March 5th, 2010, 04:35 PM
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
March 5th, 2010, 07:18 PM
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)
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
