Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Txt To Columns Correction

  1. #1
    Board Regular bs0d's Avatar
    Join Date
    Dec 2006
    Location
    Where the Hoot Owls Screw the Chickens
    Posts
    600
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Txt To Columns Correction

    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,
    -bs0d | AllSyntax

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,309
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Txt To Columns Correction

    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)?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular bs0d's Avatar
    Join Date
    Dec 2006
    Location
    Where the Hoot Owls Screw the Chickens
    Posts
    600
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Re: Txt To Columns Correction

    Quote Originally Posted by Joe4 View Post
    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.
    -bs0d | AllSyntax

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,309
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Txt To Columns Correction

    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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,445
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    23 Thread(s)

    Default Re: Txt To Columns Correction

    Quote Originally Posted by bs0d View Post
    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:
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular bs0d's Avatar
    Join Date
    Dec 2006
    Location
    Where the Hoot Owls Screw the Chickens
    Posts
    600
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Txt To Columns Correction

    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 by bs0d; Jun 11th, 2018 at 01:51 PM.
    -bs0d | AllSyntax

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,445
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    23 Thread(s)

    Default Re: Txt To Columns Correction

    Quote Originally Posted by bs0d View Post
    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:
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,309
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Txt To Columns Correction

    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!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,445
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    23 Thread(s)

    Default Re: Txt To Columns Correction

    Quote Originally Posted by Joe4 View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    Board Regular bs0d's Avatar
    Join Date
    Dec 2006
    Location
    Where the Hoot Owls Screw the Chickens
    Posts
    600
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Txt To Columns Correction

    Quote Originally Posted by Joe4 View Post
    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
    -bs0d | AllSyntax

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •