Pre-pend data to content of cells

[ProvPC]

Hi there!
I've a spreadsheet of 3500 rows that containing data that I'm preparing to import into tables in a database. Because the data was often inconsistently entered, there's a fair degree of manipulation to be done before it's ready to move to it's new home. So far (thanks to tygrrboi and Istvan Hirsch!) I've pretty much gotten the names separated and sorted out.

The next project is the phone numbers. I've manually taken care of nearly all the inconsistencies with formatting, but I'm left with one remaining issue, which sounds simple, but isn't working out the way I expected it to. I need to "pre-pend" a three-digit area code to the roughly 1500 numbers that don't already have them. In this case, it's presumed that all of the missing area codes are the same (208).

I thought I could do this with Find & Replace using wildcards (something along the lines of "find ###-#### and replace with (208) ###-####), but it doesn't seem to support wild cards. I could try to create a column of "208" and maybe use the "&" function to add the original number? But there's probably an easier way.

Any help would be appreciated!

Hm. Is "pre-pend" even a word?

 

[JoeMo]

Can you give us some examples of where the ###-#### string appears in your data entries?

 

[ProvPC]

Can you give us some examples of where the ###-#### string appears in your data entries?

Happily!
Here is an idea of what I'm working with. The data in the phone column was entered in a variety of ways, with many combinations of country codes, area codes, spaces, dashes, parentheses, alphabetic notations - or lack of any of the above. I've pretty much sorted out the variances, so what's left are in the two formats shown in the example: (111) 111-2222 or just 111-2222.

What I'm hoping to find is a simple way to "pre-pend" the area code (which will always be 208 for those that are missing) to the phone numbers that don't already have an area code listed.

 

[JoeMo]

Happily!
Here is an idea of what I'm working with. The data in the phone column was entered in a variety of ways, with many combinations of country codes, area codes, spaces, dashes, parentheses, alphabetic notations - or lack of any of the above. I've pretty much sorted out the variances, so what's left are in the two formats shown in the example: (111) 111-2222 or just 111-2222.

What I'm hoping to find is a simple way to "pre-pend" the area code (which will always be 208 for those that are missing) to the phone numbers that don't already have an area code listed.

Will there ever be more than one "-" (as in ###-####) in a cell?

 

[JoeMo]

Will there ever be more than one "-" (as in ###-####) in a cell?

Have to go offline now so I'll assume there's only one hyphen in a cell, the one that separates ### from ####.

First, with your mouse, select all the cells that may need to be modified. Then run the macro below.

Sub PrefixAreaCode()
'Select the cells you want to apply this to, then run this macro
Dim c As Range
Const Pre As String = "(208)"
Application.ScreenUpdating = False
For Each c In Selection
       If c.Value Like "* ###-#### *" Or c.Value Like "###-#### *" Or c.Value _
           Like "* ###-####" Or c.Value Like "###-####" Then
                     c.Value = Replace(c.Value, Mid(c.Value, InStr(c.Value, "-") - 3, _
                         8), Pre & Mid(c.Value, InStr(c.Value, "-") - 3, 8))
       End If
Next c
Application.ScreenUpdating = False
End Sub

 

[ProvPC]

Have to go offline now so I'll assume there's only one hyphen in a cell, the one that separates ### from ####.

First, with your mouse, select all the cells that may need to be modified. Then run the macro below.

Sub PrefixAreaCode()
'Select the cells you want to apply this to, then run this macro
Dim c As Range
Const Pre As String = "(208)"
Application.ScreenUpdating = False
For Each c In Selection
       If c.Value Like "* ###-#### *" Or c.Value Like "###-#### *" Or c.Value _
           Like "* ###-####" Or c.Value Like "###-####" Then
                     c.Value = Replace(c.Value, Mid(c.Value, InStr(c.Value, "-") - 3, _
                         8), Pre & Mid(c.Value, InStr(c.Value, "-") - 3, 8))
       End If
Next c
Application.ScreenUpdating = False
End Sub

I'm sorry for the delay! The holidays kept me away from the project. I'll give this a try - thanks for your help!

 

[ProvPC]

Have to go offline now so I'll assume there's only one hyphen in a cell, the one that separates ### from ####.

First, with your mouse, select all the cells that may need to be modified. Then run the macro below.

Sub PrefixAreaCode()
'Select the cells you want to apply this to, then run this macro
Dim c As Range
Const Pre As String = "(208)"
Application.ScreenUpdating = False
For Each c In Selection
       If c.Value Like "* ###-#### *" Or c.Value Like "###-#### *" Or c.Value _
           Like "* ###-####" Or c.Value Like "###-####" Then
                     c.Value = Replace(c.Value, Mid(c.Value, InStr(c.Value, "-") - 3, _
                         8), Pre & Mid(c.Value, InStr(c.Value, "-") - 3, 8))
       End If
Next c
Application.ScreenUpdating = False
End Sub

I'm sorry to be a pest. I'm most likely doing something incorrectly, but when I select the cells that need updating and run the macro, nothing appears to happen. There are no errors of any kind, but the prefix is not added to the cell contents, Do you have any idea what I could be doing wrong?

 

[Rick Rothstein]

I'm sorry to be a pest. I'm most likely doing something incorrectly, but when I select the cells that need updating and run the macro, nothing appears to happen. There are no errors of any kind, but the prefix is not added to the cell contents, Do you have any idea what I could be doing wrong?

I cannot help you with JoeMo's code, but I have an alternate macro for you to try. Note that you do not have to select any cells as it will process all the cells in Column E automatically.

[table="width: 500"]
[tr]
	[td]Sub PrefixAreaCode208()
  Dim Addr As String
  Addr = "E2:E" & Cells(Rows.Count, "E").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(LEFT(@)=""("",@,""(208) ""&@)", "@", Addr))
End Sub[/td]
[/tr]
[/table]

 

[ProvPC]

I cannot help you with JoeMo's code, but I have an alternate macro for you to try. Note that you do not have to select any cells as it will process all the cells in Column E automatically.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub PrefixAreaCode208()
  Dim Addr As String
  Addr = "E2:E" & Cells(Rows.Count, "E").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(LEFT(@)=""("",@,""(208) ""&@)", "@", Addr))
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Hi there! Thank you for your response!
This works very well indeed. I appreciate your help!