+ Reply to Thread
Results 1 to 6 of 6

Challenge: Split text in cell with random spaces in between each word

  1. #1
    Registered User
    Join Date
    03-29-2017
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    60

    Challenge: Split text in cell with random spaces in between each word

    I'm not sure the easiest way to go about this, but if anyone is up to the challenge or has already accomplished this for some weird reason, please help me out.

    Example text:

    Text will be placed in cells A1 and A2.

    HTML Code: 
    Each letter corresponds to some string text, such as A1 = hello B1 = good etc...

    I need to split each string into separate cells across A1 and A2.

    Cells(1,1) = A1, Cells(1,2) = B1, Cells(1,3) = C1 etc...
    Cells(2,1) = A2, Cells(2,2) = B2, Cells(2,3) = C2

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Challenge: Split text in cell with random spaces in between each word

    Have you tried selecting the column and using Text to Columns using Delimited and space?

    Make sure the box labeled "Treat consecutive delimiters as one" is checked.

    I tried this with your example and it got rid of all of the spaces and separated the values into separate cells.
    Last edited by 63falcondude; 05-25-2017 at 02:48 PM.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Challenge: Split text in cell with random spaces in between each word

    I made the For i loop just 1 to 2 and not dynamic for row numbers since you specified only 2 rows in your request, hope that suits?

    Please Login or Register  to view this content.
    Last edited by Arkadi; 05-25-2017 at 02:56 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Challenge: Split text in cell with random spaces in between each word

    Not sure I understood your description, but:
    Please Login or Register  to view this content.
    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 05-25-2017 at 03:03 PM.

  5. #5
    Registered User
    Join Date
    03-29-2017
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    60

    Re: Challenge: Split text in cell with random spaces in between each word

    That was a classy move FalconDude. It worked perfectly. But this is one part of a bigger macro. But basically, my macro will be pasting these two lines of text into a temp sheet, then it needs to separate the words, and then it uses the words separately to paste back in to a VBScript for SAP.

  6. #6
    Registered User
    Join Date
    03-29-2017
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    60

    Re: Challenge: Split text in cell with random spaces in between each word

    Arkadi-an, you nailed it. It stored as text instead of numbers but I just added a little snippet to convert it to general. Works like a beauty. Here is the full snippet incase anyone is interested.

    HTML Code: 

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] vba to split the cell values text to columns with undelimited spaces
    By MicroTees in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2016, 06:47 AM
  2. [SOLVED] Split cell text with variable spaces
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-12-2014, 03:33 PM
  3. [SOLVED] Code for random spaces for text to column in VBA
    By ndemetres in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2014, 04:38 PM
  4. [SOLVED] Replace Random Chinese Words (Not Characters) with Blank Spaces in MS Word
    By mlexcelhelpforum in forum Word Programming / VBA / Macros
    Replies: 8
    Last Post: 08-05-2012, 06:42 AM
  5. Finding certain word in cell....then split text
    By Lynx2x in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-22-2012, 08:09 AM
  6. remove random spaces in a text string
    By mariusescu in forum Excel General
    Replies: 2
    Last Post: 07-10-2008, 03:32 PM
  7. Replies: 1
    Last Post: 07-07-2005, 01:49 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1