Require formula

Ron99

Active Member
Joined
Feb 10, 2010
Messages
324
Hello,

I have data as mentioned below in each row.

,,,,,,,,,,,,,,,,AM,,,,,,,,,,,FE,,,,,,,BA
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,DD,,,,,,
HQ,,,,,,,BB,,,,,,,,,,,,,,,,,,,,,,,DF


The output that I require is as mentioned below.

AM,FE,BA
DD
HQ,BB,DF

Regards,
Vikas
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
is
,,,,,,,,,,,,,,,,AM,,,,,,,,,,,FE,,,,,,,BA
in one cell, or is it a series of cells in one row?

Also what version of Xl are you using?
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
If it is all in 1 cell then you can perform a triple substitute to remove all additional commas except 1, as such:

E.g. A1 contains ,,,,,,,AM,,,,,,,,FE,,,,,,BA then the following formula will return:

Code:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44))
Which gives ,AM,FE,BA

I appreciate this causes a comma at the start however... Using some helper cells I came up with this to clean up the other scenarios (I am sure there are more efficient ways though):

Code:
IF(RIGHT(A1,1)=CHAR(44),SUBSTITUTE(IF(LEFT(A1,1)=CHAR(44),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44)),CHAR(44),"",1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44))),CHAR(44),"",(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44)))-LEN(SUBSTITUTE(A1,CHAR(44),""))-1)),IF(LEFT(A1,1)=CHAR(44),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44)),CHAR(44),"",1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(44),CHAR(17)&CHAR(18)),CHAR(18)&CHAR(17),""),CHAR(17)&CHAR(18),CHAR(44))))
EDIT: I suppose you could also do text to columns and join with a TEXTJOIN function, if you have that function that is, depends on the excel version
 
Last edited:

Ron99

Active Member
Joined
Feb 10, 2010
Messages
324
Wow, this is great! it works exactly the way I was looking for. Thank you for your valuable time.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
Or maybe

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Another option
=SUBSTITUTE(TRIM(SUBSTITUTE(A2,","," "))," ",",")
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Wow I totally overshot this one, those formulas are way better to use than mine! Note to self: Trim is very powerful! Thanks guys

EDIT: Excel Trim is better than SQL Trim!
 
Last edited:

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top