Retreving Numbers Only out of Formula

[Dave_A]

I have this worksheet which im pulling data from the import external data part, anyway it is coming up as "space2 Mar 2007" anyway, im wondering is there any way that i can run a formula/something in a different cell to detele the space part out

the cells i need the space deleted from is f61, g61 and h61

if it can be done without the space maybe put it in like f233, g233, h233

(in between is data i dont need so its just hidden)

If someone could help that would be fantastic...

cheers

[NBVC]

Try:

=SUBSTITUTE(F61,"spacer","")

....not, not sure if you meant "spacer" or "space"....

[Dave_A]

god i love this forum... thank you very much...

one more issue though, the date is coming up as 2 Mar 2007 text not as a date

is there anyway to now make that a date format so i can run an if formula off it via dates (as i cant do that via text)?

once again thanks a heap

[NBVC]

god i love this forum... thank you very much...

one more issue though, the date is coming up as 2 Mar 2007 text not as a date

is there anyway to now make that a date format so i can run an if formula off it via dates (as i cant do that via text)?

once again thanks a heap

just add +0 to convert to true numeric...then format cell as date

=SUBSTITUTE(F61,"spacer","")+0

[Bryan Hessey]

I have this worksheet which im pulling data from the import external data part, anyway it is coming up as "space2 Mar 2007" anyway, im wondering is there any way that i can run a formula/something in a different cell to detele the space part out

the cells i need the space deleted from is f61, g61 and h61

if it can be done without the space maybe put it in like f233, g233, h233

(in between is data i dont need so its just hidden)

If someone could help that would be fantastic...

cheers

Hi,

to retain your 'spacer' and remove the spacer that is part of a cell,

select all data (the square left of A and above row number 1) and then CTRL/H (asin Edit, Replace)

Select 'Entire cell contents' and find spacer
replace with ssppaacceerr
replace all (346 in the test)

untick 'entire cell' and replace all spacer with "" (nothing)

replace all ssppaacceerr with spacer

hth
---
added, the dates appear as dates ok after this.
---

[Dave_A]

i got it to work with the top 2 suguestions...

thanks so much guys, u make life so much easier...