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))
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Browz97

New Member
Joined
Oct 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Moderator edit:
Image removed as it contained personal info
 
Last edited by a moderator:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
it's more useful for the helpers to post a small, copyable than screen shot, see my signature for info
 
Last edited by a moderator:

Browz97

New Member
Joined
Oct 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Moderator Edit:
Please do not post personal information to the board.
 
Last edited by a moderator:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

i suggest you delete both posts as they consist personal info
 
Last edited by a moderator:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
should only post with dummy data rather real data
 

Browz97

New Member
Joined
Oct 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

There was no personal data - it had all been scrambled. Only thing real was the first names. But OK.

Book1.xlsm
ABCDEFGHIJKLMN
1PhoneMobileEnquirers NumberEnquirers NameEnquirers name 1Enquirers mobile 1Enquirers phone 1Enquirers name 2Enquirers mobile 2Enquirers phone 2Text from phoneNumber from phoneText from mobileNumber from mobile
2Monkey 0203 3303333Monkey  02033303333     Monkey 02033303333
3Cponkkey 01330233333Cponkkey  01330233333     Cponkkey 01330233333
4Giraffe 07333 333333Giraffe  07333333333     Giraffe 07333333333
53333333333333333333  3333333333  333333333 333333333 3333333333
6Puma 01333 303 333Puma  01333303333     Puma 01333303333
707330 333 333 Gazelle Gazelle 07330333333      Gazelle07330333333
8Lion- 07333 333 333Lion-  07333333333     Lion- 07333333333
9Starfish- 07333 333330Starfish-  07333333330     Starfish- 07333333330
10Turtle (tone) 07333 330 013Turtle (tone)  07333330013     Turtle (tone) 07333330013
1107330 3333330133 333 0130  01333330130  07330333333 07330333333 01333330130
120133 333 3001Gas Mask(Boss) 07330 333333  07330333333Gas Mask(Boss)  01333333001 01333333001Gas Mask(Boss) 07330333333
13Riz 0 07333 333333Cookies (tone) 07333 333333Riz  07333333333Cookies (tone)  007333333333Riz 007333333333Cookies (tone) 07333333333
14Riz 0 07333 333333Cookies (tone) 07333 333333Riz  07333333333Cookies (tone)  007333333333Riz 007333333333Cookies (tone) 07333333333
15Basketball (tone) 07333 333333Basketball (tone)  07333333333     Basketball (tone) 07333333333
16Martin (Boss) 073333 33333307333 013 333Martin (Boss)  07333013333  073333333333Martin (Boss) 073333333333 07333013333
17Natasha 07333 333333The big (Boss)01333330333Natasha  01333330333The big (Boss) 07333333333Natasha 07333333333The big (Boss)01333330333
18Mr White-01333 333333Mr White-  01333333333     Mr White- 01333333333
19Simpsons 07333 073330Simpsons  07333073330     Simpsons 07333073330
20Rukia- 07333 333333Shuttle- 0133 3333300Rukia-  01333333300Shuttle-  07333333333Rukia- 07333333333Shuttle- 01333333300
Sheet1
Cell Formulas
RangeFormula
E2:E20E2=IF(ISBLANK(GetText(K2)),IF(ISBLANK(GetText(M2)),"",M2),K2)
F2:F20F2=IF(ISBLANK(K2),IF(ISBLANK(M2),"",IF(LEFT(N2,3)="07",N2,IF(LEFT(L2,3)="07",L2,""))),"")
G2:G20G2=IF(ISBLANK(E2),"",IF(LEFT(N2,3)="07","",N2))
H2:H20H2=IF(ISBLANK(GetText(E2)),"",IF(GetText(E2)=GetText(M2), "", M2))
I2:I20I2=IF(ISBLANK(GetText(H2)),"",IF(LEFT(N2,3)="07",N2,""))
J2:J20J2=IF(ISBLANK(H2),"",IF(LEFT(L2,3)="07","",L2))
K2:K20K2=IF(ISBLANK(GetText(A2)),"",GetText(A2))
L2:L20L2=IF(ISBLANK(GetNumeric(A2)),"",GetNumeric(A2))
M2:M20M2=IF(ISBLANK(GetText(B2)),"",GetText(B2))
N2:N20N2=IF(ISBLANK(GetNumeric(B2)),"",GetNumeric(B2))
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
have a look if this works for you

Cols E & F get the numbers from A & B
Cols G & H sort them out into phone and mobile, and
I & J are the correspondence texts


Book1.xlsm
ABCDEFGHIJ
1PhoneMobileEnquirers NumberEnquirers NamePhoneMobilePhone textMobile text
2023456789Monkey 0203 330333302345678902033303333023456789  Monkey
3Cponkkey 0133023333300133023333301330233333  Cponkkey
4Giraffe 07333 333333007333333333 07333333333 Giraffe
533333333333333333333333333333333333333    
6Puma 01333 303 33300133330333301333303333  Puma
707330 333 333 Gazelle007330333333 07330333333  Gazelle
8Lion- 07333 333 333007333333333 07333333333 Lion-
9Starfish- 07333 333330007333333330 07333333330 Starfish-
10Turtle (tone) 07333 330 013007333330013 07333330013 Turtle (tone)
1107330 3333330133 333 013007330333333013333301300133333013007330333333
120133 333 3001Gas Mask(Boss) 07330 33333301333333001073303333330133333300107330333333 Gas Mask(Boss)
13Riz 0 07333 333333Cookies (tone) 07333 33333300733333333307333333333 07333333333Riz Cookies (tone)
14Riz 0 07333 333333Cookies (tone) 07333 33333300733333333307333333333 07333333333Riz Cookies (tone)
15Basketball (tone) 07333 333333007333333333 07333333333 Basketball (tone)
16Martin (Boss) 073333 33333307333 013 33307333333333307333013333 073333333333Martin (Boss)
17Natasha 07333 333333The big (Boss)0133333033307333333333013333303330133333033307333333333Natasha The big (Boss)
18Mr White-01333 33333300133333333301333333333  Mr White-
19Simpsons 07333 073330007333073330 07333073330 Simpsons
20Rukia- 07333 333333Shuttle- 0133 333330007333333333013333333000133333330007333333333Rukia- Shuttle-
Sheet1
Cell Formulas
RangeFormula
E2:F20E2=GetNumeric(A2)
G2:G20G2=IF(OR(LEFT(E2,2)="01",LEFT(E2,2)="02"),E2,IF(OR(LEFT(F2,2)="01",LEFT(F2,2)="02"),F2,""))
H2:H20H2=IF(LEFT(E2,2)="07",E2,IF(LEFT(F2,2)="07",F2,""))
I2:J20I2=IF(GetText(A2)=0,"",GetText(A2))
 

KP117

New Member
Joined
Oct 22, 2020
Messages
24
Office Version
  1. 365
  2. 2019
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.
 

Browz97

New Member
Joined
Oct 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
have a look if this works for you

Cols E & F get the numbers from A & B
Cols G & H sort them out into phone and mobile, and
I & J are the correspondence texts


Book1.xlsm
ABCDEFGHIJ
1PhoneMobileEnquirers NumberEnquirers NamePhoneMobilePhone textMobile text
2023456789Monkey 0203 330333302345678902033303333023456789  Monkey
3Cponkkey 0133023333300133023333301330233333  Cponkkey
4Giraffe 07333 333333007333333333 07333333333 Giraffe
533333333333333333333333333333333333333    
6Puma 01333 303 33300133330333301333303333  Puma
707330 333 333 Gazelle007330333333 07330333333  Gazelle
8Lion- 07333 333 333007333333333 07333333333 Lion-
9Starfish- 07333 333330007333333330 07333333330 Starfish-
10Turtle (tone) 07333 330 013007333330013 07333330013 Turtle (tone)
1107330 3333330133 333 013007330333333013333301300133333013007330333333
120133 333 3001Gas Mask(Boss) 07330 33333301333333001073303333330133333300107330333333 Gas Mask(Boss)
13Riz 0 07333 333333Cookies (tone) 07333 33333300733333333307333333333 07333333333Riz Cookies (tone)
14Riz 0 07333 333333Cookies (tone) 07333 33333300733333333307333333333 07333333333Riz Cookies (tone)
15Basketball (tone) 07333 333333007333333333 07333333333 Basketball (tone)
16Martin (Boss) 073333 33333307333 013 33307333333333307333013333 073333333333Martin (Boss)
17Natasha 07333 333333The big (Boss)0133333033307333333333013333303330133333033307333333333Natasha The big (Boss)
18Mr White-01333 33333300133333333301333333333  Mr White-
19Simpsons 07333 073330007333073330 07333073330 Simpsons
20Rukia- 07333 333333Shuttle- 0133 333330007333333333013333333000133333330007333333333Rukia- Shuttle-
Sheet1
Cell Formulas
RangeFormula
E2:F20E2=GetNumeric(A2)
G2:G20G2=IF(OR(LEFT(E2,2)="01",LEFT(E2,2)="02"),E2,IF(OR(LEFT(F2,2)="01",LEFT(F2,2)="02"),F2,""))
H2:H20H2=IF(LEFT(E2,2)="07",E2,IF(LEFT(F2,2)="07",F2,""))
I2:J20I2=IF(GetText(A2)=0,"",GetText(A2))
Hi Alan,
Many thanks for the reply - apologies for the delay, I'd not seen the response.
Part of the challenge is associating the correct name with the correct number whilst also putting the number into the correct (mobile/landline) field.
For example, in row 13, both are mobile numbers and they both need to be in a mobile field. They then also need to be associated with the correct name.
Apologies if this wasn't clear in my original post.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,252
Messages
5,600,543
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