Txt To Columns Correction

[bs0d]

Sometimes I receive data that has a text to columns bust. Assume a list of accounts are in column A, and column B has numbers. In some cases with very large numbers, the first digit of the value for column B will be the last character in column A.

I could detect accounts where this happened by something like:

IF((right(trim(--cell--),1)+0)>=1, "Alert", "")

I'm wondering, what's the best way to find and fix these? It will always be a column A, B issue... so that narrows the scope and complexity a bit. I need to take the right most character and append it to the left of value in column B.

Am I forced to loop through the full data set, or is there a way I can uniquely identify and replace with a for each loop? Just looking for most efficient way to tackle this. Thanks,

 

[Joe4]

I know, it is just that I thought I had it figured out from what the OP wrote originally... obviously I didn't

Sorry, couldn't just resist giving you a hard-time, usually its the other way around!

I tried to be clear. Perhaps it was only clear in my head. At the end of the day, nothing substitutes for a visual..

To me, the ambiguity was with the phrase "list of accounts" for column A. In my mind, that could mean either numbers or text (I have seen it go both ways).
But, the commas in the formatted numbers can also complicate matters (and that wasn't originally mentioned).

That is why it is always good to see exactly what you are working with. Else, we make assumptions which really aren't indicative of the data you are actually working with.
Your picture clarified that in a hurry (without having to say much at all!).

 

[bs0d]

You should have posted that example in your original post. See if this macro works for you...

[table="width: 500"]
[tr]
	[td]Sub FixBadTextToColumns()
  Dim R As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow).NumberFormat = "#,##0"
  Range("B1:B" & LastRow) = Evaluate(Replace("IF(ISNUMBER(-RIGHT(A1:A#)),RIGHT(TRIM(A1:A#))&TRIM(B1:B#),0+B1:B#)", "#", LastRow))
  Range("A1:A" & LastRow) = Evaluate(Replace("IF(ISNUMBER(-RIGHT(A1:A#)),TRIM(LEFT(A1:A#,LEN(A1:A#)-1)),A1:A#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

This doesn't seem to work right. A few observations:

* Trailing numeric values and whitespace remain in column A.
* Both column A and B are populated with zero's for rows that have no data.
* For rows with non-numeric data in column B, the contents are converted to #VALUE ! errors.

 

[Rick Rothstein]

This doesn't seem to work right. A few observations:

* Trailing numeric values and whitespace remain in column A.
* Both column A and B are populated with zero's for rows that have no data.
* For rows with non-numeric data in column B, the contents are converted to #VALUE ! errors.

1) You never said you wanted Column A's whitespaces modified.

2) You never indicated you had blank cells.

3) You never said Column B had text in it.

Specifically for number 2 and 3 above, your first message specifically said "Assume a list of accounts are in column A, and column B has numbers."

See if this code works for you...

[table="width: 500"]
[tr]
	[td]Sub FixBadTextToColumns()
  Dim R As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow).NumberFormat = "#,##0"
  Range("B1:B" & LastRow) = Evaluate(Replace("IF(ISNUMBER(-RIGHT(TRIM(A1:A#))),RIGHT(TRIM(A1:A#))&TRIM(B1:B#),IF(TRIM(B1:B#)="""","""",0+B1:B#))", "#", LastRow))
  Range("A1:A" & LastRow) = Evaluate(Replace("IF(ISNUMBER(-RIGHT(TRIM(A1:A#))),TRIM(LEFT(TRIM(A1:A#),LEN(TRIM(A1:A#))-1)),IF(TRIM(A1:A#)="""","""",TRIM(A1:A#)))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[bs0d]

1) You never said you wanted Column A's whitespaces modified.

2) You never indicated you had blank cells.

3) You never said Column B had text in it.

Specifically for number 2 and 3 above, your first message specifically said "Assume a list of accounts are in column A, and column B has numbers."

See if this code works for you...

[table="width: 500"]
[tr]
	[td]Sub FixBadTextToColumns()
  Dim R As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow).NumberFormat = "#,##0"
  Range("B1:B" & LastRow) = Evaluate(Replace("IF(ISNUMBER(-RIGHT(TRIM(A1:A#))),RIGHT(TRIM(A1:A#))&TRIM(B1:B#),IF(TRIM(B1:B#)="""","""",0+B1:B#))", "#", LastRow))
  Range("A1:A" & LastRow) = Evaluate(Replace("IF(ISNUMBER(-RIGHT(TRIM(A1:A#))),TRIM(LEFT(TRIM(A1:A#),LEN(TRIM(A1:A#))-1)),IF(TRIM(A1:A#)="""","""",TRIM(A1:A#)))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

Hi Rick, thanks for your patience. It is still not working. Here is a link to a sample document which should provide the most clarity. And although not previously mentioned, can I reasonably limit whitespace replacement only for rows where the trailing value is detected in A and pushed to B?

http://www.allsyntax.com/images/Excel/txt-to-col-example.xlsx

Thanks,

 

[Rick Rothstein]

Hi Rick, thanks for your patience. It is still not working. Here is a link to a sample document which should provide the most clarity. And although not previously mentioned, can I reasonably limit whitespace replacement only for rows where the trailing value is detected in A and pushed to B?

http://www.allsyntax.com/images/Excel/txt-to-col-example.xlsx

You have some cells in Column A ending with a number which I suspect should retain that number and not move it over to Column B. The cells I am thinking of are labeled...

other revenue 1
expenses 2
expenses 3

Am I correct? If so, are there any other such values in Column A that might not be included in your linked sample?

 

[bs0d]

You have some cells in Column A ending with a number which I suspect should retain that number and not move it over to Column B. The cells I am thinking of are labeled...

other revenue 1
expenses 2
expenses 3

Am I correct? If so, are there any other such values in Column A that might not be included in your linked sample?

Rick -
I did have to embellish some of the account names. They're all text, you can ignore the trailing values there -- apologies. I didn't think about that.

 

[Rick Rothstein]

Rick -
I did have to embellish some of the account names. They're all text, you can ignore the trailing values there -- apologies. I didn't think about that.

Actually, if they really have numbers on the end then I cannot ignore them as my code is completely dependent on all trailing numbers belonging to Column B values. So, to ask again, do you really have text in Column A that end in numbers where those numbers are not to be moved to Column B?

 

[bs0d]

Actually, if they really have numbers on the end then I cannot ignore them as my code is completely dependent on all trailing numbers belonging to Column B values. So, to ask again, do you really have text in Column A that end in numbers where those numbers are not to be moved to Column B?

This is sort of getting old for me now. Again, I had to rename some of the accounts in the example. I made a mistake by using numbers on the end of a few. I suppose my comment "they're all text, you can ignore the trailing values there" is just not clear enough for you.

Here's my code, which works. I wanted to get away from looping. Maybe this code can serve as a universal language more easily understood:

Dim ws As Worksheet
Set ws = Worksheets("mySheet")
Dim x As Long
Dim myvar As Long
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For x = LastRow To 5 Step -1
    If IsNumeric(Right(Trim(ws.Range("A" & x).Value), 1)) = False Then
        myvar = 0
    Else
        myvar = Right(Trim(ws.Range("A" & x).Value), 1) + 0
    End If
    
    If myvar >= 1 Then
        ws.Range("B" & x).Value = myvar & ws.Range("B" & x).Value                      'Push value from col. A to B.
        ws.Range("A" & x).Value = Trim(Replace(ws.Range("A" & x).Value, myvar, ""))    'Remove value and whitespace
        
	'Highlight affected cells
        With ws.Range("A" & x & ":B" & x).Interior
            .ColorIndex = 36
        End With
    End If
Next x