Sentence seperation macro EX VB

[MATU70]

Hi

I am working on a template worksheet and will be pasting external data onto the template sheet daily then reverting back to the fresh blank template worksheet the following day.
Some of the data i am pasting is in sentence format, pasted to 1 cell, which i need that data seperated along the following columns.
I have tried using text to columns on data tab but it looks like i need a macro for Visual Basic to format the sheet, I have just started learning VB over the last week so not an expert but if someone can provide a macro so i can format the sheet, i really would appreciate it.

I have attached a sheet as example: YELLOW IS PASTED WITHOUT FORMAT/ GREY IS HOW I NEED IT TO LOOK WHEN PASTED.

Thanks for your time.

[Richard Buttrey]

Hi,

Why complicate what is a simple task by resorting to VB.

Just use the TextToColumns functionality and specify the space character as the delimiter.

[:) Sixthsense :)]

Try some thing like this...

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Please Login or Register  to view this content.

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[MATU70]

Sixthsense thanks ill give that a go.

Richard it is a template sheet which i will be using daily and i dont want to have to go through the texttocolumn routine every time i open up the sheet. unless you know of a way to format the sheet so the column in question always performs the action without having to format evertime i open up the template?

[Richard Buttrey]

In that case SixthSense's macro will perform the TextToColumns functionality for you.

You'll need to put the code in the Workbook open event (or Call it from the open event) if you want it to happen each time the workbook is opened.

[MATU70]

Sixthsense that worked thankyou but what i forgot to mention was that either side of the column formated there is extra pasted data which is not a sentence needing slip for example

DATA/DATA/SENTENCEDATA/DATA/DATA

So i need to push the columns after the sentence slip format forward so not to lose pasted data in pre or following columns.

I edited your macro to C1 as thats where the pasted data will appear, I started in C1 and what happened is your macro worked but overwrote the pasted data in A1&B1

If you can edit the macro for me that would be great thank you

[Richard Buttrey]

Hi,

The easiest way is to add say 4 columns before running the macro.
e.g. Assuming the text column to be split is column A

Please Login or Register  to view this content.

[MATU70]

Hi Richard

I have attached a sheet showing exactly how the pasted data will appear, column C is where the macro needs to split the data without overwriting other pasted data either side, The macro works and just needs editing with maybe an insert column string added to the macro.

<sub>Sub ApplyTextToColumnsInCode()

With Range("C1", Cells(Rows.Count, "C").End(xlUp))
.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Space:=True
End With

End Sub</sub>