Phone data

[Jay-d]

Hello,

I have the following problem, I have a list with the the following type of data:
FAX : 010/25878596

I need to keep the following format: 0102587596

So far I've replaced all the spaces and "FAX :" by nothing but then I only keep the following data: 102587596

I've tried formatting the data as text but this doesnt seem to work.

Any ideas?

[TMS]

=TEXT(SUBSTITUTE(SUBSTITUTE(A1,"/",""),"FAX : ",""),"00000000000")


Regards, TMS

[benishiryo]

hi Jay-d, welcome to the forum. do you mean "01025878596"? if so, try:
=SUBSTITUTE(SUBSTITUTE(A1,"FAX : ",""),"/","")

[TMS]

@benishiryo: I think the OP's problem is that when he is left with just numbers, he loses the leading zero and wishes to retain it. Using TEXT will output a text string and keep all the digits.


Regards, TMS

[Pete_UK]

Or you could do this:

=""&SUBSTITUTE(SUBSTITUTE(A1,"FAX : ",""),"/","")

Hope this helps.

Pete

[benishiryo]

@TMShucks:
hmmm but since it's already a text, my formula will not convert it to a number no? hence, the leading "0" is still retained. i might be missing something here. works fine on my side when i tried

[Jay-d]

Problem solved the solution of benishiryo worked perfectly.

Thx!

[TMS]

@benishiryo: I thought that was the problem - Excel trying to be helpful and, after taking out the text, saying, "ah, these are all numeric digits, I'll make this a number" ... and then losing the leading zero.

Whatever, Pete's solution is shorter and will have the same effect as the TEXT option.

Regards, TMS

[Pete_UK]

The problem with using TEXT with a fixed number of digits is that you might get more leading zeros than needed, if the original number is short of digits - the resulting phone number might then get interpreted as an overseas destination (in the UK anyway, where 00 at the beginning of a phone number means it is to an international destination).

Hope this helps.

Pete

[TMS]

I did count the digits, 11, to match the data sample. In that respect, it should meet the OP's requirements.

But, as I said, I think your solution works well and is much neater.

Regards, TMS