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