Sep 06 2021 09:35 AM - edited Sep 06 2021 10:27 AM
Ok so basically its a bit more complicated than that, I want to merge a phone number and home phone number into one column , but I want the home phone number to go to the next line in the merged column which also needs a foreign key to be repeated if the employee has 2 numbers entered.
Edit: I Managed to do it with a little help
Sep 06 2021 10:00 AM - edited Sep 06 2021 10:04 AM
I didn't understand the ID#
You can use the following formula and then click on "Wrap Text" option:
=TEXTJOIN(CHAR(10),,B2:C2)
Sep 06 2021 10:08 AM
SolutionRun the following macro:
Sub MergePhone()
Dim rng As Range
Application.ScreenUpdating = False
Set rng = Range("C:C").Find(What:="*", SearchDirection:=xlPrevious)
Do
rng.Offset(1).EntireRow.Insert
rng.Offset(1, -2).Value = rng.Offset(0, -2).Value
rng.Offset(1, -1).Value = "'" & rng.Value
Set rng = Range("C:C").Find(What:="*", After:=rng, SearchDirection:=xlPrevious)
If rng.Row = 1 Then Exit Do
Loop
Range("C:C").ClearContents
Application.ScreenUpdating = True
End Sub
Sep 06 2021 10:11 AM
@Deerg65 You don't specify what "a foreign key" entails. But perhaps you mean that "04" should be in the beginning of every phone number. If so, try this in D2, set the cell format to wrap text and copy it down:
=TEXT(B2,"0000000000")&CHAR(10)&IF(ISBLANK(C2),"",TEXT(LEFT(B2,1),"00"))&C2
Sep 06 2021 10:22 AM
Sep 07 2021 04:41 AM
Sep 07 2021 04:56 AM
You've never said "Why" you are doing these merges. In general, merging data like that is something people regret later, since Phone# becomes ambiguous once done....whereas "Cell" and "Home" are clear. I can't imagine why you'd want to merge six, unless you're talking the whole home address, and there too I'd want to ask, "Why?!!"
If you're doing this so as to create a printout of each person's contact info, or a nice address list, you'd be far better served, over the long term, by keeping each data element intact as separate cells and using MailMerge in Word (with the Excel database as the source) to create the printouts.
Sep 07 2021 07:18 AM
Sep 06 2021 10:08 AM
SolutionRun the following macro:
Sub MergePhone()
Dim rng As Range
Application.ScreenUpdating = False
Set rng = Range("C:C").Find(What:="*", SearchDirection:=xlPrevious)
Do
rng.Offset(1).EntireRow.Insert
rng.Offset(1, -2).Value = rng.Offset(0, -2).Value
rng.Offset(1, -1).Value = "'" & rng.Value
Set rng = Range("C:C").Find(What:="*", After:=rng, SearchDirection:=xlPrevious)
If rng.Row = 1 Then Exit Do
Loop
Range("C:C").ClearContents
Application.ScreenUpdating = True
End Sub