Solved

# Insert a Space in to excel if starting numbers match

Posted on 2011-03-21
173 Views
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!

0
Question by:Envisage-Tech
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 3

LVL 33

Expert Comment

ID: 35183056
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)

jppinto

Book1.xlsx
0

LVL 1

Author Comment

ID: 35183643
Thanks Almost there. The above example deletes a character rather than inserts a space?
0

LVL 33

Expert Comment

ID: 35183711
You're right! I had a mistake on my formula...it should have a RIGHT(A2,8) instead of RIGHT(A2,7).

jppinto
Book1.xlsx
0

LVL 1

Author Comment

ID: 35184021
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

LVL 33

Expert Comment

ID: 35184304
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

LVL 1

Author Comment

ID: 35184391
Yes and an ifnot statement that adds a space after 5 characters...
0

LVL 33

Expert Comment

ID: 35184403
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

LVL 33

Accepted Solution

jppinto earned 500 total points
ID: 35184497
=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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month4 days, 1 hour left to enroll