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]

Is the "Text to Columns" not being done correctly?
Where is the data coming from (if a file, what type of file)?
What does the data look like before "Text to Columns"?
How is the "Text to Columns" applied (what delimiter is being chose, and which data types are you choosing for each field)?

 

[bs0d]

Is the "Text to Columns" not being done correctly?
Where is the data coming from (if a file, what type of file)?
What does the data look like before "Text to Columns"?
How is the "Text to Columns" applied (what delimiter is being chose, and which data types are you choosing for each field)?

I say text-to-columns because that's how others might inadvertently end up with the same situation, if you used fixed widths and no delimiter. I can see how in a perfect world, the resolution would be to correct the process that erroneously outputs this way. Unfortunately, it's beyond my control. Let's pretend this is a black box, and this is the data I have to work with.

 

[Joe4]

OK. Please post a few examples of what your data looks like (include a few "good" records and a few "records").
Also let us know if these two columns are entered as Text or Numbers (the justification is usually a dead giveaway - text is usually left-justified while numbers are right-justified).

 

[Rick Rothstein]

I say text-to-columns because that's how others might inadvertently end up with the same situation, if you used fixed widths and no delimiter. I can see how in a perfect world, the resolution would be to correct the process that erroneously outputs this way. Unfortunately, it's beyond my control. Let's pretend this is a black box, and this is the data I have to work with.

Assuming you want to fix Columns A and B after the Text To Columns procedure has already screwed things up and assuming the extra character in Column B is always a non-digit character and assuming that extra non-digit character needs to be put back at the end of the text in Column A, give this macro a try...

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

 

[bs0d]

Rick and Joe -

An example would look like this:

Column A                Column B

Hello World  1           ,095,236
Testing                   999,875
Example   2              ,212,445

Errors are lines 1 and 3. You see the extra numeric digit as the last character for column A (column A will always be text). Column 'B' becomes text on error, since it begins with the ",".

Rick, I think the code may need to be revised as I don't believe the assumptions are correct.

to clarify:

I want to push the trailing numeric value found at the end of cells in column A to the front part of column B, because it was incorrectly cut-off. I need to incorporate trim() function as well at some point in the process due to whitespace between end of account name and the trailing numeric value. So the space between "e" of Example, and 2 needs removed.

 

[Rick Rothstein]

Rick, I think the code may need to be revised as I don't believe the assumptions are correct.

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]

 

[Joe4]

You should have posted that example in your original post.

That's why I asked for an example up in post #4 before coming up with code!

 

[Rick Rothstein]

That's why I asked for an example up in post #4 before coming up with code!

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

 

[bs0d]

That's why I asked for an example up in post #4 before coming up with code!

I tried to be clear. Perhaps it was only clear in my head. At the end of the day, nothing substitutes for a visual. I'll give the code a try. Looks like no loop required, which is nice