Values with spaces

[Salamullah]

I have a list of amount but it contained spaces before and after figures how can I get only numerical value.


like

100,000
78,900
23,000

 

[oldbrewer]

for the first one length is 9
so you want value(mid(A1,2,8))

8 is 9-1
2 is a constant
so =value(mid(a1,2,len(A1)-1))

 

[Salamullah]

Not sure why its giving #value! . My data is as follows its in text and I want only numeric value

[TABLE="width: 96"]
<tbody>[TR]
[TD]Total Cost
[/TD]
[/TR]
[TR]
[TD]1,539.53
[/TD]
[/TR]
[TR]
[TD]4,726.34
[/TD]
[/TR]
[TR]
[TD]8,119.27
[/TD]
[/TR]
[TR]
[TD]4,366.64
[/TD]
[/TR]
[TR]
[TD]100,421.84
[/TD]
[/TR]
[TR]
[TD]310.29
[/TD]
[/TR]
[TR]
[TD]16,595.38
[/TD]
[/TR]
[TR]
[TD]23,287.38
[/TD]
[/TR]
[TR]
[TD]48,513.19
[/TD]
[/TR]
[TR]
[TD]7,898.31
[/TD]
[/TR]
[TR]
[TD]81,649.00
[/TD]
[/TR]
[TR]
[TD]3,949.55
[/TD]
[/TR]
[TR]
[TD]11,637.55
[/TD]
[/TR]
[TR]
[TD]13,723.31
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Give this a try... select the column, bring up the Text To Columns dialog box (Data tab, Data Tools panel) and click the Finish button as soon as the dialog box appears... you values should be real numbers now.

 

[oldbrewer]

are you certain that all cells start and end with a blank ?

 

[Rick Rothstein]

Give this a try... select the column, change the cell formatting to General, then bring up the Text To Columns dialog box (Data tab, Data Tools panel) and click the Finish button as soon as the dialog box appears... you values should be real numbers now.

I accidentally left out the step I show in red above in my earlier message.

 

[Salamullah]

Just saw all fields contained 2 spaces after the figures no space in the starting of numbers

 

[Salamullah]

I have done but figures are appearing as same no change

 

[oldbrewer]

so =left(a1,len(a1)-2)

 

[Salamullah]

Hi Oldbrewer,

Its workable now many thanks