Separating phone numbers from text fields, keeping all the data, then moving it around a bit. And breathe.

Browz97

New Member
Joined
Oct 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello people, hope you are having a good Thursday.

I've been fiddling with this Excel sheet for about a week now and I've come to the conclusion I need help!

I am trying to clean up the data such that:

1) Only mobile numbers are in the mobile field. Mobile numbers start with "07".
2) Only landline numbers are in the landline field. Landline numbers start with "01" or "02"
3) If there is text in either of the mobile field, phone field or both fields this text and number combination are captured, separated and stored individually, but ensuring the link between the data is maintained.

To provide some context, this is a list of account holders. The account holder could have many people associated with the account.

I am working with the assumption that if the account holder is the only person associated with the account, the number in mobile and/or phone belongs to them.
I am just checking that the they are in the correct field and then moving on.

However, there are many cases where the data is very messy. For example, in many of the phone and mobile fields I have text as well as a number. This also happens in both fields for some rows.

For example, the phone field might contain "Browz97 - 0775551234" or the mobile filed might contain "01956123123 - Home? ".

In this case, I am assuming I have found an additional contact for the account holder and need to separate out the information and store it in the same row as the account holder.
I am also assuming I have a maximum of 2 alternative contacts.

So, I have started to pull the data apart and am getting stuck on moving the data to the correct places.

I've attached a dummy worksheet to highlight the problem I am facing.

Columns A - D are the original fields.

On row 2 I have correctly taken the name from the mobile field and place it in column E. I have also taken the number and placed it in Column G.

All is OK down to row 4(!).

On row 5, there are no text details so there should be no data in columns G or H.

On rows 7, 8, 9 and 10 the mobile numbers are not pulling through to column F.

In row 11 I need to swap the numbers as they are in the incorrect fields. B11 is a landline and should be in phone, A11 is a mobile and should be in the mobile field.

In row 12, it has correctly captured the name and put it into E12, number into F12. However, it has taken the phone number from A12 and put it in J12 when there is no name to accompany it.

Then on row 13 - my brain explodes. I want it to look like row 14, but just can't get it to work.

Happy to provide any more detail if necessary and any help happily received.

Functions used :

VBA Code:
Function GetNumeric(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function


Function GetText(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i

GetText = Result

End Function

Formula in column E - =IF(ISBLANK(GetText(K2)),IF(ISBLANK(GetText(M2)),"",M2),K2)
Formula in column F - =IF(ISBLANK(K2),IF(ISBLANK(M2),"",IF(LEFT(N2,2)="07",N2,IF(LEFT(L2,2)="07",L2,""))),"")
Formula in column G - =IF(ISBLANK(E2),"",IF(LEFT(N2,2)="07","",N2))
Formula in column H - =IF(ISBLANK(GetText(E2)),"",IF(GetText(E2)=GetText(M2), "", M2))
Formula in column I - =IF(ISBLANK(GetText(H2)),"",IF(LEFT(N2,2)="07",N2,""))
Formula in column J - =IF(ISBLANK(H2),"",IF(LEFT(L2,2)="07","",L2))
K- =IF(ISBLANK(GetText(A2)),"",GetText(A2))
L - =IF(ISBLANK(GetNumeric(A2)),"",GetNumeric(A2))
M - =IF(ISBLANK(GetText(B2)),"",GetText(B2))
N - =IF(ISBLANK(GetNumeric(B2)),"",GetNumeric(B2))
 

Browz97

New Member
Joined
Oct 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Assume column C,D,E,F as Phone no.(Landline), Name_Phone no., Mobile no., Name_Mobile no..
At C2 and down, try this
=IF(ISERR(AGGREGATE(14,6,SEARCH({"01";"02"},CONCAT(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AZ$1),1),""))),1)),"",CONCAT(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AZ$1),1),"")))&" "&IF(ISERR(AGGREGATE(14,6,SEARCH({"01";"02"},CONCAT(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AZ$1),1),""))),1)),"",CONCAT(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AZ$1),1),"")))

D2 and down
=IF(ISERR(AGGREGATE(14,6,--SEARCH({"-01";"-02"},"-"&TEXTJOIN("",TRUE,IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1),""))),1)),"",CONCAT(IF(ISNUMBER(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1)),"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1))))&" "&IF(ISERR(AGGREGATE(14,6,--SEARCH({"-01";"-02"},"-"&TEXTJOIN("",TRUE,IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1),""))),1)),"",CONCAT(IF(ISNUMBER(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1)),"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1))))

E2 and down
=IF(ISERR(AGGREGATE(14,6,SEARCH("07",CONCAT(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AZ$1),1),""))),1)),"",CONCAT(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AZ$1),1),"")))&" "&IF(ISERR(AGGREGATE(14,6,SEARCH("07",CONCAT(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AZ$1),1),""))),1)),"",CONCAT(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AZ$1),1),"")))

F2 and down
=IF(ISERR(AGGREGATE(14,6,--SEARCH("-07","-"&TEXTJOIN("",TRUE,IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1),""))),1)),"",CONCAT(IF(ISNUMBER(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1)),"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1))))&" "&IF(ISERR(AGGREGATE(14,6,--SEARCH("-07","-"&TEXTJOIN("",TRUE,IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1),""))),1)),"",CONCAT(IF(ISNUMBER(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1)),"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," 0 ","")," ",""),"-",""),COLUMN($A$1:$AX$1),1))))

These formulas are not very smart, not perfect. Hope it's close to your expectation.
Hey @KP117 ,
Many thanks, these work well however, I have a problem on row 13, for example, where there are mobile numbers in both cols A and B.
The mobile numbers are both added to the same field (mobile no.) and the names are also both added to the same field (Name_Mobile no.).
Might this need to be a 2 step process?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

KP117

New Member
Joined
Oct 22, 2020
Messages
24
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm afraid so. Otherwise, if possible with little further effort, you may manually edit the raw data that cause problems, like those in row 13.
At this point that's all I can do.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,251
Messages
5,600,541
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top