Envisage-Tech
asked on
Insert a Space in to excel if starting numbers match
I have a list of telephone numbers and I wish to format them with a space , I wish to add a space after the 5th character if the first 5 characters match a set number of numbers.
e.g the numbers
441289307028
441482869861
442083032228
If any of the above numbers start with "4420","4423","4424","4428 " then format them with a space after the 5th character.
So the results would be
441289307028
441482869861
44208 3032228
Any help would be great!
e.g the numbers
441289307028
441482869861
442083032228
If any of the above numbers start with "4420","4423","4424","4428
So the results would be
441289307028
441482869861
44208 3032228
Any help would be great!
ASKER
Thanks Almost there. The above example deletes a character rather than inserts a space?
You're right! I had a mistake on my formula...it should have a RIGHT(A2,8) instead of RIGHT(A2,7).
Please check the attached file.
jppinto
Book1.xlsx
Please check the attached file.
jppinto
Book1.xlsx
ASKER
Thank You just played around with it - do you mind If I ask you a further question?
All the numbers I have are telephone numbers and I wish to put them in the correct UK format , 3 out of the 4 formulas for the different types of telcode are below
=IF(OR(LEFT(A1,4)="4420",L EFT(A1,4)= "4423",LEF T(A1,4)="4 424",LEFT( A1,4)="442 8"),LEFT(A 1,4)&" " & RIGHT(A1,8),A1)
=IF(OR(LEFT(A1,7)="4413873 ",LEFT(A1, 7)="441524 2",LEFT(A1 ,7)="44153 94",LEFT(A 1,7)="4415 395",LEFT( A1,7)="441 5396",LEFT (A1,7)="44 16973",LEF T(A1,7)="4 416974",LE FT(A1,7)=" 4416977",L EFT(A1,7)= "4417683", LEFT(A1,7) ="4417684" ,LEFT(A1,7 )="4417687 ",LEFT(A1, 7)="441946 7"),LEFT(A 1,4)&" " & RIGHT(A1,8),A1)
=IF(OR(LEFT(A1,6)="44113", LEFT(A1,6) ="44114",L EFT(A1,6)= "44115",LE FT(A1,6)=" 44116",LEF T(A1,6)="4 4117",LEFT (A1,6)="44 118",LEFT( A1,6)="441 21",LEFT(A 1,6)="4413 1",LEFT(A1 ,6)="44141 ",LEFT(A1, 6)="44151" ,LEFT(A1,6 )="44161", LEFT(A1,6) ="44191"), LEFT(A1,6) &" " & RIGHT(A1,8),A1)
The fourth type just needs a space added after the 5th character which is a majority of all the numbers.
How do I put these formulas all together so that it produces the correct results for all types of numbers? Or am I floggin a dead horse! ;)
Thank You
All the numbers I have are telephone numbers and I wish to put them in the correct UK format , 3 out of the 4 formulas for the different types of telcode are below
=IF(OR(LEFT(A1,4)="4420",L
=IF(OR(LEFT(A1,7)="4413873
=IF(OR(LEFT(A1,6)="44113",
The fourth type just needs a space added after the 5th character which is a majority of all the numbers.
How do I put these formulas all together so that it produces the correct results for all types of numbers? Or am I floggin a dead horse! ;)
Thank You
Hummm...I'm not sure I understood what you're trying to do now...you want to put the 3 formulas posted all in one formula?
ASKER
Yes and an ifnot statement that adds a space after 5 characters...
Just the first 3 formulas would look something like this:
=IF(OR(LEFT(A1,4)="4420",L EFT(A1,4)= "4423",LEF T(A1,4)="4 424",LEFT( A1,4)="442 8"),LEFT(A 1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1, 7)="441387 3",LEFT(A1 ,7)="44152 42",LEFT(A 1,7)="4415 394",LEFT( A1,7)="441 5395",LEFT (A1,7)="44 15396",LEF T(A1,7)="4 416973",LE FT(A1,7)=" 4416974",L EFT(A1,7)= "4416977", LEFT(A1,7) ="4417683" ,LEFT(A1,7 )="4417684 ",LEFT(A1, 7)="441768 7",LEFT(A1 ,7)="44194 67"),LEFT( A1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1, 6)="44113" ,LEFT(A1,6 )="44114", LEFT(A1,6) ="44115",L EFT(A1,6)= "44116",LE FT(A1,6)=" 44117",LEF T(A1,6)="4 4118",LEFT (A1,6)="44 121",LEFT( A1,6)="441 31",LEFT(A 1,6)="4414 1",LEFT(A1 ,6)="44151 ",LEFT(A1, 6)="44161" ,LEFT(A1,6 )="44191") ,LEFT(A1,6 )&" " & RIGHT(A1,8),A1)))
If you want to add an aditional formula, it would look even worst!
=IF(OR(LEFT(A1,4)="4420",L
If you want to add an aditional formula, it would look even worst!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
=IF(OR(LEFT(A1,4)="4420",L
Please check the attached example.
jppinto
Book1.xlsx