HELP! Split text with Excel funcions

gio123bg

Active Member
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.

Regards,

Giovanni

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

here goes for the third part that is for CCCCCC

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

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
5AAAA
6BBBB
7CCCCC
Split text

Hi All,

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.

Regards,

Giovanni

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
BCDE
4 AAAA * BBBBBBB * CCCCC DDDD EEEE FFFFAAAABBBBBBBCCCCC DDDD EEEE FFFF
Split text

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

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
BCDE
4 AAAA * BBBBBBB * CCCCC DDDD EEEE FFFFAAAABBBBBBBCCCCC DDDD EEEE FFFF
Split text (2)

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

Did you try the Text to Columns as well?

Replies
0
Views
129
Replies
1
Views
137
Replies
1
Views
682
Replies
19
Views
703
Replies
1
Views
247

1,207,259
Messages
6,077,347
Members
446,279
Latest member
hoangquan2310

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back