Splitting 1 cell into 2 based on underscore in text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

Can someone please help me?

I am trying to split one cell into two by dividing it at the first
underscore in the text.
I don't know how many characters will be before or after each first
underscore.

For example, Cell A below would be divided into cells B and C with the first
underscore being eliminated.

Cell A Cell B Cell C

1-DEPRASST_TRANS 1-DEPRASST TRANS
1-ACC2-DEPR_TRANS 1-ACC2-DEPR TRANS
2-OTHDED_SHOLD_EXP 2-OTHDED SHOLD_EXP
1-OTHCURRL_DEFINCOME 1-OTHCURRL DEFINCOME

I've been trying all kinds of formulas in cells B and C with no luck. This
has been driving me crazy.

Thank you in advance.

RJF
 
Try this:

Select the cells from Col_A
<data><text-to-columns>
Select: Delimited.....[Next]
Delimiters: Other (enter the underscore _ )....[Next]
Destination: select the top cell of the range to want to contain the parsed
values. If your base list begins in A1, you may want the parsed values to
begin in B1.

That will parse the Col_A values into Col_B and Col_C

Does that help?
 
Highlight your columm. Then use Date > Text to Columns, Delimited, click
Next, under Delimiters, check Other and two an underscore. Click Next, under
Column data format select General or Text, then Finish.
 
Thank you for your response,

I tried that and the problem is there is sometimes more than one underscore
in the cell. I only need it to parse at the first underscore and keep the
other underscores in the text in column C. Using the text to columns parses
it out to column D, etc.

Ron Coderre said:
Try this:

Select the cells from Col_A
<data><text-to-columns>
Select: Delimited.....[Next]
Delimiters: Other (enter the underscore _ )....[Next]
Destination: select the top cell of the range to want to contain the parsed
values. If your base list begins in A1, you may want the parsed values to
begin in B1.

That will parse the Col_A values into Col_B and Col_C

Does that help?
-----------------------
Regards,
Ron

XL2002, WinXP-Pro


RJF said:
Hello All,

Can someone please help me?

I am trying to split one cell into two by dividing it at the first
underscore in the text.
I don't know how many characters will be before or after each first
underscore.

For example, Cell A below would be divided into cells B and C with the first
underscore being eliminated.

Cell A Cell B Cell C

1-DEPRASST_TRANS 1-DEPRASST TRANS
1-ACC2-DEPR_TRANS 1-ACC2-DEPR TRANS
2-OTHDED_SHOLD_EXP 2-OTHDED SHOLD_EXP
1-OTHCURRL_DEFINCOME 1-OTHCURRL DEFINCOME

I've been trying all kinds of formulas in cells B and C with no luck. This
has been driving me crazy.

Thank you in advance.

RJF
Click to expand...
Click to expand...
 
OK, RJF (I didn't pay close enough attention to your examples).....
Try this:

For a value in A1

B1: =LEFT(A1,SEARCH("_",A1)-1)
C1: =SUBSTITUTE(A1,B1&"_","",1)

Copy those formulas down as far as needed.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


RJF said:
Thank you for your response,

I tried that and the problem is there is sometimes more than one underscore
in the cell. I only need it to parse at the first underscore and keep the
other underscores in the text in column C. Using the text to columns parses
it out to column D, etc.

Ron Coderre said:
Try this:

Select the cells from Col_A
<data><text-to-columns>
Select: Delimited.....[Next]
Delimiters: Other (enter the underscore _ )....[Next]
Destination: select the top cell of the range to want to contain the parsed
values. If your base list begins in A1, you may want the parsed values to
begin in B1.

That will parse the Col_A values into Col_B and Col_C

Does that help?
-----------------------
Regards,
Ron

XL2002, WinXP-Pro


RJF said:
Hello All,

Can someone please help me?

I am trying to split one cell into two by dividing it at the first
underscore in the text.
I don't know how many characters will be before or after each first
underscore.

For example, Cell A below would be divided into cells B and C with the first
underscore being eliminated.

Cell A Cell B Cell C

1-DEPRASST_TRANS 1-DEPRASST TRANS
1-ACC2-DEPR_TRANS 1-ACC2-DEPR TRANS
2-OTHDED_SHOLD_EXP 2-OTHDED SHOLD_EXP
1-OTHCURRL_DEFINCOME 1-OTHCURRL DEFINCOME

I've been trying all kinds of formulas in cells B and C with no luck. This
has been driving me crazy.

Thank you in advance.

RJF
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ron.

That worked perfectly. You can't imagine how much I appreciate it.

rjf

Ron Coderre said:
OK, RJF (I didn't pay close enough attention to your examples).....
Try this:

For a value in A1

B1: =LEFT(A1,SEARCH("_",A1)-1)
C1: =SUBSTITUTE(A1,B1&"_","",1)

Copy those formulas down as far as needed.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


RJF said:
Thank you for your response,

I tried that and the problem is there is sometimes more than one underscore
in the cell. I only need it to parse at the first underscore and keep the
other underscores in the text in column C. Using the text to columns parses
it out to column D, etc.

Ron Coderre said:
Try this:

Select the cells from Col_A
<data><text-to-columns>
Select: Delimited.....[Next]
Delimiters: Other (enter the underscore _ )....[Next]
Destination: select the top cell of the range to want to contain the parsed
values. If your base list begins in A1, you may want the parsed values to
begin in B1.

That will parse the Col_A values into Col_B and Col_C

Does that help?
-----------------------
Regards,
Ron

XL2002, WinXP-Pro


:

Hello All,

Can someone please help me?

I am trying to split one cell into two by dividing it at the first
underscore in the text.
I don't know how many characters will be before or after each first
underscore.

For example, Cell A below would be divided into cells B and C with the first
underscore being eliminated.

Cell A Cell B Cell C

1-DEPRASST_TRANS 1-DEPRASST TRANS
1-ACC2-DEPR_TRANS 1-ACC2-DEPR TRANS
2-OTHDED_SHOLD_EXP 2-OTHDED SHOLD_EXP
1-OTHCURRL_DEFINCOME 1-OTHCURRL DEFINCOME

I've been trying all kinds of formulas in cells B and C with no luck. This
has been driving me crazy.

Thank you in advance.

RJF
Click to expand...
Click to expand...
Click to expand...
 
Back
Top