Separate out a string

[Klb1503]

Hi,

I have strings of text where the components are separated out by an underscore. I need to separate the strings out in to there components and in to separate columns, preferably without using macro's.

Does anyone have a formula that could help with this please?

Any help would be very much appreciated

Thank you

EDA_EMEAI_France_3006
EDA_FC_EMEAI_Germany_3251
EDA_NR_UK&I_Ireland_2700

Would become:
EDA
EMEAI
France
3006

or

EDA
FC
EMEAI
Germany
3251

They should be in separate columns on the same row but I can't work out how to paste a table

 

[Klb1503]

I can't, the string is downloaded from a system that we enter data in to, I can't alter it

 

[Joe4]

I can't, the string is downloaded from a system that we enter data in to, I can't alter it

But you are looking for an Excel formula, right?
Doesn't that mean that the file is then being downloaded to Excel, where you can use Search and Replace?

 

[Klb1503]

It is being downloaded to Excel, I was hoping to get a formula to split it out rather than having to reconfigure the original data and do text to columns. The string comes down for around 15 - 20,000 lines and I was hoping to avoid search and replace and sorts etc. But It doesn't look as if it can be done by a formula so I will have to look at alternatives.

Thanks for looking anyway

 

[Joe4]

I'm sure it can be done by formulas, but they will be pretty complex. And I am not sure I understand how adding these formulas and copying them down for every Excel file you download is any better than doing two quick Search and Replaces, and then running a Text to Columns. It really should only take a few minutes to do it.

If it were me, I would create an Excel macro to do the two steps I suggested. Then it could run by a single-click on each file, running much quicker than the other alternatives.

 

[taurean]

Following arrangement works given if all formulas are applied in sequence i.e. all 4 depend on one another

Excel Workbook
	A	B	C	D	E
1	EDA_EMEAI_France_3006	EDA	EMEAI	France	3006
2	EDA_FC_EMEAI_Germany_3251	EDA_FC	EMEAI	Germany	3251
3	EDA_NR_UK&I_Ireland_2700	EDA_NR	UK&I	Ireland	2700
Sheet1

Formulas


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(ISERROR(FIND("FC",A1,1)),IF(ISERROR(FIND("NR",A1,1)),LEFT(A1,3),LEFT(A1,6)),LEFT(A1,6))</TD></TR><TR><TD>C1</TD><TD>=MID(A1,LEN(B1)+2,(FIND("_",A1,LEN(B1)+2)-(LEN(B1)+2)))</TD></TR><TR><TD>D1</TD><TD>=MID(A1,(LEN(B1)+LEN(C1)+3),FIND("_",A1,(LEN(B1)+LEN(C1)+3))-(LEN(B1)+LEN(C1)+3))</TD></TR><TR><TD>E1</TD><TD>=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+LEN(D1)+3))</TD></TR></TABLE></TD></TR></TABLE>