TRIM-MID-SUBSTITUTE TO KEEP CHARACTERS IN THE 2ND DELIMINATED COLUMN

[JuicyMusic]

Hi Guru's, I have a formula that I found on another post from FLUFF here on MrExcel. I would like help adjusting it. I DOWNLOADED XL2BB SO PLEASE TELL ME IF IT IS WORKING.

Here is the formula I am needing adjustment to:

=TRIM(MID(SUBSTITUTE("*"&$I8,"*",REPT(" ",100)),COLUMNS($J8:J8)*100,100))

I would like the characters to remain in the 2nd column that is deliminated. If there is only an "*" in the original data, then column 2 should be blank.

Here is the original data I am working with - the next 2 columns are showing the results I would like to see:

		DESIRED RESULTS - col 1	DESIRED RESULTS - col 2
ORIGINAL DATA TO TEXT DELIMINATE		CITY NAMES (in new 1st column)	CITY NOTE (in new 2nd column)
Adelanto*		Adelanto
Alberhill (Lake Elsinore*)		Alberhill	(Lake Elsinore*)
Alta Loma (Rancho Cucamonga*)		Alta Loma	(Rancho Cucamonga*)
Alviso (San Jose*)		Alviso	(San Jose*)
Amargosa (Death Valley)		Amargosa	(Death Valley)
Bay Point (formally West Pittsburg)		Bay Point	(formally West Pittsburg)
Beverly Hills*		Beverly Hills
Big Bear Lake*		Big Bear Lake
Coto De Caza		Coto De Caza
Crest Park		Crest Park
Daggett		Daggett
Davis* (U.C. Davis campus rate is 7.25%)		Davis	(U.C. Davis campus rate is 7.25%)
Del Sur		Del Sur
Dos Rios		Dos Rios
Santa Barbara* (U.C. Santa Barbara campus rate is 7.75%)		Santa Barbara	(U.C. Santa Barbara campus rate is 7.75%)

Thank you everyone,
Juicy

 

[jasonb75]

Looking at your sample, I think you're trying to overcomplicate things. See if this works.
I can't test it at the moment, hopefully no typos in the formulas.
1st column

=IFERROR(TRIM(SUBSTITUTE(LEFT(I8,FIND("(",I8)-1),"*","")),I8)

2nd column

=IFERROR(MID(I8,FIND("(",I8),LEN(I8)),"")

 

[Peter_SSs]

I DOWNLOADED XL2BB SO PLEASE TELL ME IF IT IS WORKING.

It is not working. You need to follow the steps here carefully.

I can't test it at the moment, hopefully no typos in the formulas.

Not a typo but I think that you have the SUBSTITUTE in the first one in the wrong place since the desired results shown have no asterisks in the first column.

=SUBSTITUTE(IFERROR(TRIM(LEFT(I8,FIND("(",I8)-1)),I8),"*","")

@JuicyMusic
A couple of alternatives to consider also

22 04 23.xlsm
	I	J	K	L
7	ORIGINAL DATA TO TEXT DELIMINATE		CITY NAMES	CITY NOTE
8	Adelanto*		Adelanto
9	Alberhill (Lake Elsinore*)		Alberhill	(Lake Elsinore*)
10	Alta Loma (Rancho Cucamonga*)		Alta Loma	(Rancho Cucamonga*)
11	Alviso (San Jose*)		Alviso	(San Jose*)
12	Amargosa (Death Valley)		Amargosa	(Death Valley)
13	Bay Point (formally West Pittsburg)		Bay Point	(formally West Pittsburg)
14	Beverly Hills*		Beverly Hills
15	Big Bear Lake*		Big Bear Lake
16	Coto De Caza		Coto De Caza
17	Crest Park		Crest Park
18	Daggett		Daggett
19	Davis* (U.C. Davis campus rate is 7.25%)		Davis	(U.C. Davis campus rate is 7.25%)
20	Del Sur		Del Sur
21	Dos Rios		Dos Rios
22	Santa Barbara* (U.C. Santa Barbara campus rate is 7.75%)		Santa Barbara	(U.C. Santa Barbara campus rate is 7.75%)
Split Columns

Cell Formulas
Range	Formula
K8:K22	K8	=TRIM(SUBSTITUTE(SUBSTITUTE(I8,L8,""),"*",""))
L8:L22	L8	=MID(I8,FIND("(",I8&"("),LEN(I8))

 

[Rick Rothstein]

A couple of alternatives to consider also

Here is a possible alternative for cell K8 (I have no idea if it might be more efficient or not)...

=LEFT(I8,MIN(FIND({"*","("," ("},I8&"* (")-1))

If there will always be a space in front of the opening parenthesis (when present), then this slightly shorter formula can be used instead...

=LEFT(I8,MIN(FIND({"*"," ("},I8&"* (")-1))

 

[Peter_SSs]

Rick, what did you do with that quote?!?!

 

[JuicyMusic]

Here is a possible alternative for cell K8 (I have no idea if it might be more efficient or not)...

=LEFT(I8,MIN(FIND({"*","("," ("},I8&"* (")-1))

If there will always be a space in front of the opening parenthesis (when present), then this slightly shorter formula can be used instead...

=LEFT(I8,MIN(FIND({"*"," ("},I8&"* (")-1))

Hello Rick, This is data from the city and they don't seem to have a standard way of adding spaces and/or characters. Your suggestion will work perfectly for another project I'm working on.

I did use your first suggestion for column K:

=LEFT(I8,MIN(FIND({"*","("," ("},I8&"* (")-1))

And I used Peter's 2nd formula for column L:

=MID(I8,FIND("(",I8&"("),LEN(I8))

It looks like this way K and L are not dependent on each other. Thank you for explaining what my original formula was doing. I understand it now.
SOLVED by Rick and Peter

Thank you so much!
Juicy

 

[Peter_SSs]

Glad we could help to get something that you are satisfied with. Thanks for the follow-up.