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!






LVL 1
Envisage-TechAsked:
Who is Participating?
 
jppintoCommented:
=IF(OR(LEFT(A1,4)="4420",LEFT(A1,4)="4423",LEFT(A1,4)="4424",LEFT(A1,4)="4428"),LEFT(A1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1,7)="4413873",LEFT(A1,7)="4415242",LEFT(A1,7)="4415394",LEFT(A1,7)="4415395",LEFT(A1,7)="4415396",LEFT(A1,7)="4416973",LEFT(A1,7)="4416974",LEFT(A1,7)="4416977",LEFT(A1,7)="4417683",LEFT(A1,7)="4417684",LEFT(A1,7)="4417687",LEFT(A1,7)="4419467"),LEFT(A1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1,6)="44113",LEFT(A1,6)="44114",LEFT(A1,6)="44115",LEFT(A1,6)="44116",LEFT(A1,6)="44117",LEFT(A1,6)="44118",LEFT(A1,6)="44121",LEFT(A1,6)="44131",LEFT(A1,6)="44141",LEFT(A1,6)="44151",LEFT(A1,6)="44161",LEFT(A1,6)="44191"),LEFT(A1,6)&" " & RIGHT(A1,8),LEFT(A1,4)& " " & RIGHT(A1,8))))

0
 
jppintoCommented:
You can use this formula on a second column:

=IF(OR(LEFT(A1,4)="4420",LEFT(A1,4)="4423",LEFT(A1,4)="4424",LEFT(A1,4)="4428"),LEFT(A1;4)&" " & RIGHT(A1,7),A1)

Please check the attached example.

jppinto


Book1.xlsx
0
 
Envisage-TechAuthor Commented:
Thanks Almost there. The above example deletes a character rather than inserts a space?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
jppintoCommented:
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
0
 
Envisage-TechAuthor Commented:
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",LEFT(A1,4)="4423",LEFT(A1,4)="4424",LEFT(A1,4)="4428"),LEFT(A1,4)&" " & RIGHT(A1,8),A1)

=IF(OR(LEFT(A1,7)="4413873",LEFT(A1,7)="4415242",LEFT(A1,7)="4415394",LEFT(A1,7)="4415395",LEFT(A1,7)="4415396",LEFT(A1,7)="4416973",LEFT(A1,7)="4416974",LEFT(A1,7)="4416977",LEFT(A1,7)="4417683",LEFT(A1,7)="4417684",LEFT(A1,7)="4417687",LEFT(A1,7)="4419467"),LEFT(A1,4)&" " & RIGHT(A1,8),A1)

=IF(OR(LEFT(A1,6)="44113",LEFT(A1,6)="44114",LEFT(A1,6)="44115",LEFT(A1,6)="44116",LEFT(A1,6)="44117",LEFT(A1,6)="44118",LEFT(A1,6)="44121",LEFT(A1,6)="44131",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


0
 
jppintoCommented:
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?
0
 
Envisage-TechAuthor Commented:
Yes and an ifnot statement that adds a space after 5 characters...
0
 
jppintoCommented:
Just the first 3 formulas would look something like this:

=IF(OR(LEFT(A1,4)="4420",LEFT(A1,4)="4423",LEFT(A1,4)="4424",LEFT(A1,4)="4428"),LEFT(A1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1,7)="4413873",LEFT(A1,7)="4415242",LEFT(A1,7)="4415394",LEFT(A1,7)="4415395",LEFT(A1,7)="4415396",LEFT(A1,7)="4416973",LEFT(A1,7)="4416974",LEFT(A1,7)="4416977",LEFT(A1,7)="4417683",LEFT(A1,7)="4417684",LEFT(A1,7)="4417687",LEFT(A1,7)="4419467"),LEFT(A1,4)&" " & RIGHT(A1,8),IF(OR(LEFT(A1,6)="44113",LEFT(A1,6)="44114",LEFT(A1,6)="44115",LEFT(A1,6)="44116",LEFT(A1,6)="44117",LEFT(A1,6)="44118",LEFT(A1,6)="44121",LEFT(A1,6)="44131",LEFT(A1,6)="44141",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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.