convert a text string to a number

  • Thread starter Thread starter kevcar40
  • Start date Start date
K

kevcar40

Hi
i have a column of data in the form of a string
ie 123 i want to convert this to a number
the problem is that the sting has a space at the beginning
How do i remove the space and convert this string to a number

thanks
kevin
 
Hi,

Easiest way: enter a single 1 in any cell. Copy it (ctrl+c). Select the
range, you want converted. Select edit/paste special. Check values under
paste and multiply under operation. Click OK.

/Sune
 
I would say the easiest way would be to use data>text to columns, then click
finish
 
Yes, one might argue that :)

(Okay, you'r right. But the other method isn't all that difficult either.)

/Sune
 
Yes, one might argue that :)

(Okay, you'r right. But the other method isn't all that difficult either.)

/Sune





- Show quoted text -
Click to expand...

THanks both
this does not remove the leading blank space
 
Perhaps it isn't a space (character 32) but rather a non-breaking
space (character 160), especially if you have copied the data from an
HTML source (web-site). You could try this formula in an adjacent
cell:

=RIGHT(A1,LEN(A1)-1)*1

Then copy the formula down for as many values as you have in column A.
You can then fix the values by highlighting all the cells with the
formula in, click <copy> then Edit | Paste Special | Values (check) |
OK then <Esc> or <Enter>. Then you can delete the original column.

Hope this helps.

Pete
 
Perhaps it isn't a space (character 32) but rather a non-breaking
space (character 160), especially if you have copied the data from an
HTML source (web-site). You could try this formula in an adjacent
cell:

=RIGHT(A1,LEN(A1)-1)*1

Then copy the formula down for as many values as you have in column A.
You can then fix the values by highlighting all the cells with the
formula in, click <copy> then Edit | Paste Special | Values (check) |
OK then <Esc> or <Enter>. Then you can delete the original column.

Hope this helps.

Pete





- Show quoted text -
Click to expand...
Worked a treat

thank you
 
Back
Top