excel formula

zap

can anyone help me out with a formula to a number eg 0831234567 and convert it to 00353831234567

SwampThing

Because of the leading 0, I'm assuming the cells are formatted as text.

=IF(LEFT(A1,1) = "0", "00353" & MID(A3,2,99), "A1")

Jebubs

zap wrote:

can anyone help me out with a formula to a number eg 0831234567 and convert it to 00353831234567

Ok....

Your best bet is the following. Format your cells as text. To keep the "0" at the start.
COL A: 00353
COL B : 086123456
COL C: =MID(B1,2,8) Returns 86123456
Select COL C
Choose copy then edit then "paste special" then Values. (Pastes all the data in COL C as values rather then Formula.

COL D =CONCATENATE(A1,C1) This joins the Data in A1 (00353) and C1 (86123456)

Hope that is what you were after.

BlackWizard

^^^^

Thats how I would of went about doing it. Im not an expert in excel though.

SwampThing

Jebubs wrote:

Ok....

Your best bet is the following. Format your cells as text. To keep the "0" at the start.
COL A: 00353
COL B : 086123456
COL C: =MID(B1,2,8) Returns 86123456
Select COL C
Choose copy then edit then "paste special" then Values. (Pastes all the data in COL C as values rather then Formula.

COL D =CONCATENATE(A1,C1) This joins the Data in A1 (00353) and C1 (86123456)

Hope that is what you were after.

Is that not a bit convoluted? It involves filling Col A with '00353' for each row that has a phone number in it and a copy here, paste special there.

What if the phone numbers are not a uniform lenght? What if one is missing the leading zero?

BlackWizard

If it does the job...