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

 

[Joe4]

Highlight the column, then go to Text To Columns under the data menu, select the Demilited option and enter the underscore as the delimiter next to the Other box. Select the format of each field and click Finish.

 

[Klb1503]

Hi,

Thanks for your reply but that won't work as not all the strings have the things in the same order for example i have:

EDA_NR_EMEAI_France and EDA_EMEAI_France and I would need the EMEAI parts to appear in the same column and the France parts to appear in the same column but because on string has an NR in it and one doesn't using text to columns doesn't work.

I sort of need a formula that looks at the string and works backward to say go to the end of the string and return everything to the right of the last underscore and then everything between the last and the next underscore and so on

Thanks

 

[Joe4]

Thanks for your reply but that won't work as not all the strings have the things in the same order for example i have:

EDA_NR_EMEAI_France and EDA_EMEAI_France and I would need the EMEAI parts to appear in the same column and the France parts to appear in the same column but because on string has an NR in it and one doesn't using text to columns doesn't work.

I sort of need a formula that looks at the string and works backward to say go to the end of the string and return everything to the right of the last underscore and then everything between the last and the next underscore and so on

Ahhh, you are learning the importance of asking a good, complete question. My reply did exactly what you originally asked, though it appears that what you originally asked was not exactly what you wanted!

Programming is very literal. In order to program anything, you need to first clearly define all the rules.

- How many columns do you expect to end up with?

- If you work backwards, then the shorter entries may have "EDA" in the second column, unless you can come up with a "hard-and-fast" rule that clearly defined when to "skip" a column. So what are those rules?

- If these rules become complex, it may actually end up being easier to do it using macros instead of formulas.

 

[Klb1503]

Hi Joe,

Thanks every so much for coming back to me, I really appreciate it as I am really struggling with this.

I would like to end up with 4 columns, working backwards based on the original strings, I would ideally have columns showing, 3006, France, EMEAI, and for the last column I don't mind if it's a mixture of NR and FC from the longer string and EDA from the shorter string.

I was given the formula
=SUBSTITUTE(RIGHT(SUBSTITUTE(C7,"_",REPT("_",LEN(C7))),LEN(C7)),"_","")

which works ok to separate out the last part of the string but I am not sure how to get the other 3 parts

Thank you so much for your help

 

[Joe4]

So, does every string start with "EDA_" and is "EDA" what you want in your first column?

 

[Klb1503]

Hi,

Everything does start with EDA, but the EDA part of the string is not really significant, I really need to extract the NR/FC, EMEAI, France, 3006 parts of the string

Thanks

Karen

 

[Joe4]

OK, still not quite clear.
What about the first example you posted, that does not have an FC or NR in it? Do you not need those records at all?

Also, do your entries always end with a 4 character string?
If so, instead of using your long formula, you could just use:
=RIGHT(C7,4)

 

[Klb1503]

Hi,

Sorry for being a bit unclear, the last part of the string won't always be 4 characters, it will vary.

The string will always start with one of 3 varations, EDA_NR, EDA_FC or just EDA_, I don't need the EDA bit separated out, just everything after it.

Thanks
Karen

 

[Joe4]

Here is an idea which could make this very easy:

Replace "EDA_NR" and "EDA_FC " with something like "EDA-NR" and "EDA-FC", respectively. Then every row will have the exact same number of delimiters, and Text to Columns will work (using the underscore as your delimiter).