# Insert Spaces In Telephone Numbers

Aug 24, 2007I have a column that contains all telephone numbers without any spaces - 01234567890

I need to have at least one space inserted anywhere in the number i.e. 0 1234567890 or 01234 567890.

I have a column that contains all telephone numbers without any spaces - 01234567890

I need to have at least one space inserted anywhere in the number i.e. 0 1234567890 or 01234 567890.

ADVERTISEMENT

Is it possible to format cells so that inputed tel. numbers are all have the same format and not just appear the same, regardless of how they were typed in?

i need it so the column can be sorded by phone #.

I have a limited amount of VBA knowledge. My limited knowledge is based on solving my problems by looking at the answers already on this site and trying to adapt it to my needs. Here goes:

I import a list of address and telephone numbers (100+) into excel. example - Cell A1 contains "10 downing street, whitehall, london, SW1A 2AA 01234 567890", sometimes there is another space after the number, sometimes there is no telephone number. I want to remove the telephone number at the end if it is there. Have searched the forums have have come up with the follow solution:

1. import list to column A

2. copy and paste Trim to column B

3. copy values in column B and paste to column A

4. delete column B

5. remove the end numbers

6. copy and paste Trim to column B

7. copy values in column B and paste to column A

8. delete column B

9. remove the end numbers

It looks like this in code:

Sub testa()

'select (=TRIM(A1)) formula and paste to sheet

Sheets("Formulas").Select

Columns("B:B").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Sheet4").Select

Columns("B:B").Select

ActiveSheet.Paste

[Code]...

'copy values of column B to Column A and delete column B

Columns("B:B").Select

Selection.Copy

Columns("A:A").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Columns("B:B").Select

Application.CutCopyMode = False

Selection.ClearContents

Range("A1").Select

[Code]...

'copy values of column B to Column A and delete column B

Columns("B:B").Select

Selection.Copy

Columns("A:A").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Columns("B:B").Select

Application.CutCopyMode = False

Selection.ClearContents

Range("A1").Select

End Sub

I know this is a long winded way around (still chuffed I worked it out by myself though!). If I merge all the macros into one marco it continually loops and deletes everything in the cells and I have to press escape to stop it.

My questions are:

1. Why, when I merge all the macros doesnt it work the same as individual macros? (I would like to know for future reference).

2. Its there a better way to remove the telephone numbers?

I have some cells which contain telephone number and some text data. They look like this.

(713) 555-4600 (Work)

(713) 555-4620 (Home)

(832) 555-4610 (John's Phone)

I need to strip out the text data and just display the phone number as follows

(713) 555-4600

(713) 555-4620

(832) 555-4610

I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.

There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.

NAME, PHONE, STATE

Bleh, 555-555-5555, =ChkState(B2)

I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field

Function ChkState(pVal As String) As Long

Dim AreaCode As String

Dim StateAbrv As String

AreaCode = Left(pVal, 3)

If AreaCode = "201" Then

StateAbrv = "Test201"

ElseIf AreaCode = "203" Then

StateAbrv = "Test203"

ElseIf AreaCode = "555" Then

StateAbrv = "Test555"

Else

StateAbrv = "0"

End If

MsgBox StateAbrv

End Function

I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.

I am having trouble trying to figure out how to insert blank cells:

I need to insert cells, in columns B through H, equal to the number of returned IDs in column B.

Please take a look at the spreadsheet I attached.

Here is the code I am using to separate the IDs in column B so that I get one ID in each A cell. When I do this it separates them great, but then I have to manually go in and insert cells down so that the rest of the rows match up with the right ID.

Sub Macro1()

Dim fromCol As String

Dim toCol As String

Dim fromRow As String

Dim toRow As String

[Code] .....

sample of separate.xlsm

I have multiple columns with many rows of unique text in each. Here is an example of what the 1st few rows of column a and b might look like:

A___________B

hboc______dds

jk________optg

hbv_______pl

FIRST:

I would like to be able to sort the rows by the character length in a column. So, it would look like this (if sorted by 1st column):

A___________B

jk_________optg

hbv________pl

hboc_______dds

Second.........

I have an instance where I have a list of numbers in different rows with different amounts and I want to add up the last 6 numbers, they're all in the same column however they're not all in consecutive rows.

Also, the gaps between the rows is not consistent.

The example below explains what I mean (I've add numbers in between so you can see the varying gaps, but they're actually blank cells)

(B1)ADD ME = 2

(B2)**blank cell**

(B3)**blank cell**

(B4)**blank cell**

(B5)**blank cell**

(B6)ADD ME = 5

(B7)**blank cell**

(B8)**blank cell**

(B9)**blank cell**

(B10)**blank cell**

(B11)**blank cell**

(B12)**blank cell**

(B13)**blank cell**

(B14)**blank cell**.........................

I have rows with "numbers" like 1 250,30 and 1 350,50, but they aren't in number format (I guess this is the problem). I am trying to get rid of the extra space between the "numbers" but the substitute or trim function does not work for me. I also tried to divide and multiply the numbers but it does not work.

I want to get the extra space of and to the number format.

I wrote numbers in column b cells in disordered way :

1

.. 1

1

.... 1

.. 1

1

I would like to make them to be aligned in the left side by a code.

I have a SS that has 20,000 rows. One of the columns contains numbers. I'm tryiny to filter out the numbers that are NOT 7 digits long. The problem i have is that some of the cells have space in between the numbers. e.g 240 1332, 432 2443, 234 2345 234 etc. And some are normal e.g 4234535, 53596835, 3459284, 2423 etc. So, filtering between ranges isn't working i think because numbers with spaces in them aren't read as numbers in excel.

View 4 Replies View RelatedI have a credit card number problem where I am trying to trim the data so that I get rid of the spaces between the numbers (number sequence look like this: 5999 9999 9999 9999. This is currently formatted as text, and I would like to maintain this as text and not convert it to a number. The trim function can't seem to get rid of the spaces.

View 4 Replies View RelatedIs there a way to see if a textbox is blank.. what I mean is, there may be some spaces in the text box which make it seem that there is something in the Textbox but all they are, are blank spaces.. no numbers in otherwords..

On a userform I have a textbox7 to enter in only the last 4 digits of the persons shop phone #. I have it coded that if the textbox isn't empty, it will put the required extension "X-" in front of it to identify it as a phone extension. If the cell is empty, it will remove it so it doesn't put the X- in the database without a phone #.

Finally the problem, if there are spaces in textbox7, no numbers, it will think that something is in there and put X- .. this messes up the database.

How do I check the textbox to see if it has numbers in there.. This is my code now...

' If TextBox7 is empty Then Phone = Blank

If TextBox7.Value = "" Then

PhoneC = " "

Else

' Preps Phone number with X- for extension

Fourdigit = TextBox7

Plus = "X-"

PhoneC = Plus + Fourdigit

End If

I have inserted a set of numbers from the web, and some are appearing with $, and others with commas, and also some with spaces before the numbers. I can remove the $ and commas fine using crtl replace but cant get rid of the spaces before the numbers? I seem to remember in word there is a way to do this using crt+replace (using $ or ^ sign perhaps?) but need my memory jogged.

View 5 Replies View RelatedI am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).

Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this :

=CONCATENATE(B4:B127)

How do I add spaces to a range?

My problem is very similar to the question asked in this thread [URL] .... but I am using numbers and would like to separate them with spaces.

I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).

Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this =CONCATENATE(B4:B127), how do I add spaces to a range?

my problem is very similar to the question asked in this thread http://www.excelforum.com/excel-work...ncatenate.html

but I am using numbers and would like to separate them with spaces.

This has definitely been done before, but I am not sure if it has been done through Excel/VBA.

I have a 9 or 10 digit telephone number, preceded by a zero and what I want to do is get Excel to figure out the combinations of text characters that will allow me to have a text phone number.

For Example:

0-800-43573375 is the same as 0-800-HELPDESK

I have a spreadsheet with Mobile phone numbers in the following format:

+44(0)77 7296 5210

The spreadsheet has 2500 of these phone numbers.

Is there a way to remove the brackets and the +44 to leave:

07772965210

I am using Microsoft Office for Mac 2011

I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.

example:

1

2

3

4

8

9

becomes

1

2

3

4

8

9

I'm using Excel 2007 and have a spreadsheet with 2500 rows. What I would like to do is insert the row number into a blank cell in each row. This will become a unique ID# for each row that I need to import this into a database later.

View 3 Replies View RelatedI need a formula to take a 6-Digit Number and change it into a 7-Digit Number. Probably a if statement would better because i have 7 digit numbers in the same column with 6-digit numbers. I need to insert zero between the 4th digit and the 5th digit of a 6-digit number. For Example:............

View 5 Replies View RelatedI would like to insert a space after a series of numbers using VBA, ie:

A00000ABC = A00000 ABC

A111AAA = A111 AAA

I have a column of part numbers in the format 530020109 but I would like to change to 530 0201 09.

View 4 Replies View RelatedI have a column of numbers that look like this: 111222111222111 and I need them to look like this: 111 222 111 222 111. The numbers are all random and not 111222.

Then I have a second column with numbers like this: 11112222111122221111 but they need to be like this: 1111 2222 1111 2222 1111.

I'm looking to insert a comma between letters and numbers like seen below using Windows 7 and Excel 2013. I'd prefer to do this through a formula and not a VBA script. I know this is easy to do if you know how many characters strings are going to be and the format stays consistent.

ABCD90bj10r7

ABCD,90,bj,10,r,7

ABCD90rt8r7

ABCD,90,rt,8,r,7

iFH15jr7ri12

iFH,15,jr,7,ri,12

iFG155jr8ri11

iFG,155,jr,8,ri,11

iFG15jr8ri9

iFG,15,jr,8,ri,9

I have a string of numbers ex. 1234, 5678, 91011 in 400 rows. Before each string I would like to add 88.

View 4 Replies View RelatedI am trying to insert the missing numbers/rows on a numerical acsending column. I have a spreadsheet that has a list of 8000 parts that I want to put into numerical acsending order, however numbers are missing. For Example:

Column A is a numerical part number and column B is the description for the part number in column A. My spreadsheet used to be in perfect numerical order however over the years some rows have been deleted which makes my spreadsheet look like the below example.

ColumnA ColumnB

1 -------Part A

2 -------Part B

3 -------Part C

5 -------Part E

6 -------Part F

As you can see part number 4 is missing is there a way to insert the missing part numbers into column A and keep column B in order associated with column A? My goal would have the result look like this below.

ColumnA ColumnB

1 -------Part A

2 -------Part B

3 -------Part C

4

5 -------Part E

6 -------Part F

I am looking for a quick solution for this as there are 8000 rows. I have tried the MATCH function and can't seem to get that to work.

The excel file has industrial information arranged by sector. The sectors are represented by six digit codes that are not strictly sequential. For example, a typical example of consecutive codes is 120011,120012,120030,120040,130011. There are upwards of three hundred sectors.

The data I am using is missing certain sectors - sometimes more than 30 at a time. I would like to find a quick way to identify the missing sectors and insert rows there, preferably with the first cell in the row being the missing sectoral code.

I have a similar problems with columns as well. But I can always just transpose the data and use whatever solution works for the rows.

I have a huge spreadsheet with addresses in column A and number in column B. Each address needs to have a ceirtain amount of lines inserted below, depending upon the number in column B. for example:................

With the new space inserted. the numbers i have range from 34 -2. does anyone have any code that will insert the number of rows depending on the number in column B? i HAVE had a look through the forums, but there are so many topics!

Need a function that would insert a letter or a number in front of numbers in a cell for example

column A

3245

I want to insert the prefix "S" in front of the nummbers 3245. so i would hopefully end up with

Column A

S3245

Copyrights 2005-15 www.BigResource.com, All rights reserved