Close gaps

[dgavin]

in colum c i have mobile numbers which have a space after the 5th digit.

e.g. 07777 555555

What formula can i use to close these gaps?

Thanks

 

[VoG]

Try

=SUBSTITUTE(C1," ","")

 

[dgavin]

Thank you

is there a way to then copy the amended data into another column WITHOUT the formula. Therefore just the taking the mobile number?

 

[VoG]

Yes, copy the amended values, go to another column and Edit > Paste Special, tick Values and click OK. You can then delete the column with the formula.

 

[dgavin]

Thanks,

Now I need a VLOOKUP will...

Look for A2 in Sheet1 column G, if it is there then = "Active" if its not there then "Non Active"

Thanks

 

[VoG]

Try

=IF(ISNUMBER(MATCH(A2,Sheet1!G:G,0)),"Active","Non Active")