Cleaning data

[Tracemark]

I have data which appears as: 447596125369
44 appears in all cells
I need to replace 44 with 0 and add a space to result in 07596 125369

I have 500,000 records so find / replace is not an option

Can anyone help?

 

[Tracemark]

Apologies.

Cells are formatted as Number

 

[Rick Rothstein]

Apologies.

Cells are formatted as Number

That is okay, as it turns out, the code I gave you will work whether the cells are formatted as Text or Number... but note that after the macro has finished, the cells will be formatted as Text.

 

[Tracemark]

I will try this.

Thank you immensely

 

[Tracemark]

It works perfectly.

Saves me about 4 weeks work. Many thanks.

Unfortunately I will be back soon when I come to sort my multi-formatted dates column.

Thanks again Rick.

 

[Tracemark]

Sorry to bother you again,

I have hit some numbers which require '121' replacing with '0' as opposed to the original '44'

Is there a slight adjustment I can make to the code to do this?

 

[Rick Rothstein]

Sorry to bother you again,

I have hit some numbers which require '121' replacing with '0' as opposed to the original '44'

Is there a slight adjustment I can make to the code to do this?

When I asked you this question...

2) Are the numbers that you want to change always 12 digits long?

you answered "Yes". Given that, what part of the number is being replaced with what in order to leave you with an 11-digit number (format ##### ######) afterwards? Or did you answer "Yes" by mistake and the numbers starting with 121 are actually 13 digits long? If so, then will you have other 13-digit numbers that do not start with 121 and what should they look like afterwards?

 

[Tracemark]

When I asked you this question...

2) Are the numbers that you want to change always 12 digits long?

you answered "Yes". Given that, what part of the number is being replaced with what in order to leave you with an 11-digit number (format ##### ######) afterwards? Or did you answer "Yes" by mistake and the numbers starting with 121 are actually 13 digits long? If so, then will you have other 13-digit numbers that do not start with 121 and what should they look like afterwards?

The answer was true this morning but I have been served with further data this afternoon.

They appear as '121##########' and I need them to appear '0#### ######'

 

[Tracemark]

When I asked you this question...

2) Are the numbers that you want to change always 12 digits long?

you answered "Yes". Given that, what part of the number is being replaced with what in order to leave you with an 11-digit number (format ##### ######) afterwards? Or did you answer "Yes" by mistake and the numbers starting with 121 are actually 13 digits long? If so, then will you have other 13-digit numbers that do not start with 121 and what should they look like afterwards?

The answer was true this morning but I have been served with further data this afternoon.

They appear as '121##########' and I need them to appear '0#### ######'

 

[Rick Rothstein]

The answer was true this morning but I have been served with further data this afternoon.

They appear as '121##########' and I need them to appear '0#### ######'

The following macro (replace the previous I gave you with this one) assumes that if the length of the number is 12, then it must be that length because it starts with 44... and that if the length of the number is 13, then it must be that length because it starts with 121 (that is, there are no other possibilities for the number to be either of those lengths)...

Sub Replace44Or121With0InsertSpace()
  Dim R As Long, C As Long, Nums As Variant
  Const RangeWithNumbers As String = "A1:C16"
  Nums = Range(RangeWithNumbers)
  For R = 1 To UBound(Nums)
    For C = 1 To UBound(Nums, 2)
      Nums(R, C) = Replace(Nums(R, C), " ", "")
      If Len(Nums(R, C)) = 12 Then
        Mid(Nums(R, C), 1) = " 0"
      ElseIf Len(Nums(R, C)) = 13 Then
        Mid(Nums(R, C), 1) = "  0"
      End If
      Nums(R, C) = Format(Trim(Nums(R, C)), "00000 000000")
    Next
  Next
  Range(RangeWithNumbers) = Nums
End Sub

 

[Tracemark]

Thanks Rick, this works perfectly.

You have been a great help