HELP! Split text with Excel funcions

[gio123bg]

Hi All,
I' m trying to split this text (3 data with "*" as separator).

(blanks)AAAA(blanks)*(blanks)BBBB(blanks)*(blanks)CCCCCC


Let's suppose to have this text in cell B4

In cell B5 I have set this formula (to have only value AAAA) first field in a different column

=LEFT(TRIM(B4);SEARCH("*";B4))

In cell B6 I have set this formula (to have only value BBBB) second field in a different column

=TRIM(MID(TRIM(B4);SEARCH("*";TRIM(B4))+2;LEN(TRIM(B4))))

But +2 or +1 must not be set because the number of blanks is not defined

In cell B7 (last column for the split) .... I need Help.

Any suggestion for a different way to solve my problem will be well appreciated.

Thanks in advance for your kind support.

Regards,

Giovanni

 

[niceguy_jay]

for first part that is for AAA you can use this formula...

=MID(TRIM(B4),1,FIND(" ",TRIM(B4)))

respond if this works so that I can think for the same in second and third part

 

[niceguy_jay]

here goes for the third part that is for CCCCCC

=RIGHT(SUBSTITUTE(B4," ",""),FIND("*",SUBSTITUTE(B4," ",""))-1)

 

[Peter_SSs]

Giovanni

Suggestion 1
Not exactly the layout you seem to want, but you could consider using Excel's built-in Text to Columns feature. Use 'Delimited' with both space and * (in the 'Other' box) as delimiters and 'Treat consecutive delimiters as one' selected.

If you need more detailed help with this suggestion, post back with what Excel version you are using.


Suggestion 2
Here's one way you can use one formula copied for all three sections. Formula in B5 is copied down.

Excel Workbook
	B
4	AAAA * BBBB * CCCCC
5	AAAA
6	BBBB
7	CCCCC
Split text

 

[gio123bg]

Hi All,
First of all thanks for your quick reply and support.

OK for formula (first column)
=MID(TRIM(B4);1;FIND(" ";TRIM(B4)))

I wait for your help for column 2 ...

For column 3 with formula

=RIGHT(SUBSTITUTE(B4;" ";"");FIND("*";SUBSTITUTE(B4;" ";""))-1)

is not exactly what I expect because I want to keep the full text after the second "*".
For example

AAAA * BBBBBBB * CCCCC DDDD EEEE FFFF

I need in column 3 "CCCCC DDDD EEEE FFFF"

I need to split the text in cell B4 in cell C4, D4 and E4. The split by column not by row.

I hope in your new help.

Thanks in advance for your kind support.

Regards,

Giovanni

 

[Peter_SSs]

I need to split the text in cell B4 in cell C4, D4 and E4. The split by column not by row.

OK, you originally said you wanted the results in B5, B6 and B7.

So, the Text to Columns would now suit better.

However, if you do want to use formulas, a few small changes to my earlier formula give this one which can be copied across.

Excel Workbook
	B	C	D	E
4	AAAA * BBBBBBB * CCCCC DDDD EEEE FFFF	AAAA	BBBBBBB	CCCCC DDDD EEEE FFFF
Split text

 

[niceguy_jay]

did you try the formula posted by Peter_SSs just above your last post ??

 

[Peter_SSs]

The advantage of my last formula is that it can be just copied across. However, if you want some shorter/simpler formulas, you could consider these three separate ones.

Excel Workbook
	B	C	D	E
4	AAAA * BBBBBBB * CCCCC DDDD EEEE FFFF	AAAA	BBBBBBB	CCCCC DDDD EEEE FFFF
Split text (2)

 

[gio123bg]

Very very good!!!!

I have copied the formula

=TRIM(MID(SUBSTITUTE(TRIM($B4);"*";REPT(" ";100));(COLUMNS($C4:E4)-1)*100+1;100))

in cells: C4, D4 and E4 and the text is correctly split!.

Thanks again for your kind support.

Regards,

Giovanni

 

[Peter_SSs]

Did you try the Text to Columns as well?