Extracting text from cell into different columns without using "Text to Columns" function

[striker359]

Hi, I would like to extract the text from a cell into different columns.

Eg. In cell I32, I have

SLVT920H SLVT921H SLVT923H SLVT935H SLVT978A SLVT910H SLVT911H SLVT979H SLVT982H

I would like to extract the text and put them into different columns.


End Result:

SLVT920H | SLVT921H | SLVT923H | SLVT978A | etc.

How should I go about doing it?

 

[Momentman]

The formula I posted should be pasted in cell B1, then you drag it across the columns, I assume each string is separated by a space, then it should work

its good to see XOR LX's formula, I need to take it to the formula auditor to see whats happening

 

[XOR LX]

The formula I posted should be pasted in cell B1, then you drag it across the columns, I assume each string is separated by a space, then it should work

its good to see XOR LX's formula, I need to take it to the formula auditor to see whats happening

Thanks! I'd be happy to try to explain it if you don't get anywhere with the auditor.

Cheers

 

[Momentman]

Thanks! I'd be happy to try to explain it if you don't get anywhere with the auditor.

Cheers

It would nice to get an explanation, if you don't mind.

Thanks

 

[striker359]

I second that notion :D

It would nice to get an explanation, if you don't mind.

Thanks

 

[striker359]

@XOR LX By VBA, I'm assuming u mean using macros yeah? If so, would you be able to construct something out?

This is my file:
https://drive.google.com/file/d/0B1biaP-f5X6zcUo0bGpxMGc2T2c/edit?usp=sharing

While the extraction function worked great, I have encountered a few inconveniences which would be great if you could iron them out.

1. For some jobs, they actually have 3 lines worth of dependencies and I was wondering if it's possible to have the macro check this and then output it into a single row instead. (Eg Row16,17,18)
2. Would there be a way to copy the strikethrough format or if that's not possible maybe to return a null value when there is a strikethrough
3. For dependencies that actually do not contain the jobname, to return a null value as well (Eg I18)

No. Not without VBA.

Regards

 

[gwono]

First thing it does it substitute 1 space with 113 spaces (not sure why 113 and not another larger number, but not the point)

It uses mid to determine where to start. As there have been a large number of spaces inserted, I guess there is a large tolerable error in what character to start at so the formula just picks up a point where the next 113 characters will contain the next block of text. This isolates the text and then trims it. As such, the formula will breakdown where string characters - number of words >113.

 

[striker359]

A simple solution would just be to change 113 to a larger number, say 1000, no?

First thing it does it substitute 1 space with 113 spaces (not sure why 113 and not another larger number, but not the point)

It uses mid to determine where to start. As there have been a large number of spaces inserted, I guess there is a large tolerable error in what character to start at so the formula just picks up a point where the next 113 characters will contain the next block of text. This isolates the text and then trims it. As such, the formula will breakdown where string characters - number of words >113.

 

[XOR LX]

First thing it does it substitute 1 space with 113 spaces (not sure why 113 and not another larger number, but not the point)
As such, the formula will breakdown where string characters - number of words >113.

A simple solution would just be to change 113 to a larger number, say 1000, no?

You're correct. My choice of 113 was arbitrary and, strictly-speaking, could fail.

Even better than choosing an even larger arbitrary value would be to replace 113 with e.g. LEN(A1) - which you'll also see often - since no part of a string can be of greater length than the string itself.

I guess I just did a quick mental calculation that the probability of there being a substring of length>113 in the OP's data was so small as to warrant my choice. But perhaps I will start using this LEN construction from now on - rigour in Excel formulas is a desirable quality, after all.

And gwono - thanks for doing my explanation for me!

Cheers

 

[striker359]

So it becomes this?

=IFERROR(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*(COLUMNS($A:A)-1)+1,LEN($A1))),"")

You're correct. My choice of 113 was arbitrary and, strictly-speaking, could fail.

Even better than choosing an even larger arbitrary value would be to replace 113 with e.g. LEN(A1) - which you'll also see often - since no part of a string can be of greater length than the string itself.

I guess I just did a quick mental calculation that the probability of there being a substring of length>113 in the OP's data was so small as to warrant my choice. But perhaps I will start using this LEN construction from now on - rigour in Excel formulas is a desirable quality, after all.

And gwono - thanks for doing my explanation for me!

Cheers

 

[XOR LX]

Correct.

Regards