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,
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,437
Office Version
365
Platform
Windows
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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,437
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,924
Office Version
2010
Platform
Windows
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]
 

bs0d

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,924
Office Version
2010
Platform
Windows
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]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,437
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,924
Office Version
2010
Platform
Windows
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.:cry:
 

bs0d

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

Watch MrExcel Video

Forum statistics

Threads
1,099,365
Messages
5,468,191
Members
406,570
Latest member
Ktvu2006

This Week's Hot Topics

Top