Split contents of a cell into several cells (space as a delimiter)

[ramky79]

Hi,
I have the following problem:

In my excel sheet cell D80 contains the following values

/bin/rm -f /home/mbprwork/start/ADP2/*_daily.adp2

I have recorded the following macro to split the contents of this cell into adjacent cells (here i'm takling space " " as my delimiter)

Please Login or Register  to view this content.

The above code is splitting the values as expected ; however there is a problem with the "-f" in "/bin/rm -f /home/mbprwork/start/ADP2/*_daily.adp2"
Once i run the code the "-f" is referenced as "#NAME?" in cell E80;
How do i retain my original value "-f" in E80 cell.

Any Help is greatly appreciated; thanks for your time.

[TMS]

Please Login or Register  to view this content.

Regards

[ramky79]

I have tried with this code but my minus options are still being referenced as #NAME?

[TMS]

I recorded it as I completed the task manually. See the attached .jpg file.

The important part is setting the second field as text.

The raw code is:

Please Login or Register  to view this content.

If this doesn't work for you, you need to call in the cavalry. It worked for me in a clean, empty Excel 2003 workbook.

I suggest that you post a sample workbook with your current code.

Regards

[snb]

Please Login or Register  to view this content.

[TMS]

The White Knight armed with some lateral thinking :-)

[snb]

Too much honour, your honour (blushing & bowing)

[TMS]

@snb: "blushing & bowing" ... that paints a picture. Just waiting for the OP to come back and say the minus sign is essential ;-)

[ramky79]

Hi TMShucks,
your solution works for me, thank you so much.

[TMS]

You're welcome. Thanks for the feedback.

[contactprabha]

Hi,
Please help me out in the following...

Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 2), Array(3, 1)), TrailingMinusNumbers:=True

In the above how do I replace B1 to any random cell in a worksheet? i.e. I just need the data to be split on adjacent cells of a selection no matter where I use the macro on a sheet.

Say for example,
In one worksheet I have the data to split at a1 and I need the split data in a1, a2, a3 etc.
And in the next worksheet at d3 and I need the split data in d3, d4, d5, etc.

[TMS]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[contactprabha]

sorry didn't know, will start a new thread.