Txt To Columns Correction

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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:

Code:
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,
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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)?
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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...
Code:
[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]
 
Upvote 0
Rick and Joe -

An example would look like this:
Code:
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.
 
Last edited:
Upvote 0
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...
Code:
[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]
 
Upvote 0
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!;)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top