software

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 ACell BCell C

1-DEPRASST_TRANS1-DEPRASSTTRANS
1-ACC2-DEPR_TRANS1-ACC2-DEPRTRANS
2-OTHDED_SHOLD_EXP2-OTHDEDSHOLD_EXP
1-OTHCURRL_DEFINCOME1-OTHCURRLDEFINCOME

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.

RJFTry this:

Select the cells from Col_A
lt;datagt;lt;text-to-columnsgt;
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-Proquot;RJFquot; wrote:

gt; Hello All,
gt;
gt; Can someone please help me?
gt;
gt; I am trying to split one cell into two by dividing it at the first
gt; underscore in the text.
gt; I don't know how many characters will be before or after each first
gt; underscore.
gt;
gt; For example, Cell A below would be divided into cells B and C with the first
gt; underscore being eliminated.
gt;
gt; Cell ACell BCell C
gt;
gt; 1-DEPRASST_TRANS1-DEPRASSTTRANS
gt; 1-ACC2-DEPR_TRANS1-ACC2-DEPRTRANS
gt; 2-OTHDED_SHOLD_EXP2-OTHDEDSHOLD_EXP
gt; 1-OTHCURRL_DEFINCOME1-OTHCURRLDEFINCOME
gt;
gt; I've been trying all kinds of formulas in cells B and C with no luck. This
gt; has been driving me crazy.
gt;
gt; Thank you in advance.
gt;
gt; RJF
gt;

Highlight your columm. Then use Date gt; 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.

quot;RJFquot; wrote:

gt; Hello All,
gt;
gt; Can someone please help me?
gt;
gt; I am trying to split one cell into two by dividing it at the first
gt; underscore in the text.
gt; I don't know how many characters will be before or after each first
gt; underscore.
gt;
gt; For example, Cell A below would be divided into cells B and C with the first
gt; underscore being eliminated.
gt;
gt; Cell ACell BCell C
gt;
gt; 1-DEPRASST_TRANS1-DEPRASSTTRANS
gt; 1-ACC2-DEPR_TRANS1-ACC2-DEPRTRANS
gt; 2-OTHDED_SHOLD_EXP2-OTHDEDSHOLD_EXP
gt; 1-OTHCURRL_DEFINCOME1-OTHCURRLDEFINCOME
gt;
gt; I've been trying all kinds of formulas in cells B and C with no luck. This
gt; has been driving me crazy.
gt;
gt; Thank you in advance.
gt;
gt; RJF
gt;

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.

quot;Ron Coderrequot; wrote:

gt; Try this:
gt;
gt; Select the cells from Col_A
gt; lt;datagt;lt;text-to-columnsgt;
gt; Select: Delimited.....[Next]
gt; Delimiters: Other (enter the underscore _ )....[Next]
gt; Destination: select the top cell of the range to want to contain the parsed
gt; values. If your base list begins in A1, you may want the parsed values to
gt; begin in B1.
gt;
gt; That will parse the Col_A values into Col_B and Col_C
gt;
gt; Does that help?
gt; -----------------------
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;RJFquot; wrote:
gt;
gt; gt; Hello All,
gt; gt;
gt; gt; Can someone please help me?
gt; gt;
gt; gt; I am trying to split one cell into two by dividing it at the first
gt; gt; underscore in the text.
gt; gt; I don't know how many characters will be before or after each first
gt; gt; underscore.
gt; gt;
gt; gt; For example, Cell A below would be divided into cells B and C with the first
gt; gt; underscore being eliminated.
gt; gt;
gt; gt; Cell ACell BCell C
gt; gt;
gt; gt; 1-DEPRASST_TRANS1-DEPRASSTTRANS
gt; gt; 1-ACC2-DEPR_TRANS1-ACC2-DEPRTRANS
gt; gt; 2-OTHDED_SHOLD_EXP2-OTHDEDSHOLD_EXP
gt; gt; 1-OTHCURRL_DEFINCOME1-OTHCURRLDEFINCOME
gt; gt;
gt; gt; I've been trying all kinds of formulas in cells B and C with no luck. This
gt; gt; has been driving me crazy.
gt; gt;
gt; gt; Thank you in advance.
gt; gt;
gt; gt; RJF
gt; gt;

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

For a value in A1

B1: =LEFT(A1,SEARCH(quot;_quot;,A1)-1)
C1: =SUBSTITUTE(A1,B1amp;quot;_quot;,quot;quot;,1)

Copy those formulas down as far as needed.

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

XL2002, WinXPquot;RJFquot; wrote:

gt; Thank you for your response,
gt;
gt; I tried that and the problem is there is sometimes more than one underscore
gt; in the cell. I only need it to parse at the first underscore and keep the
gt; other underscores in the text in column C. Using the text to columns parses
gt; it out to column D, etc.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; Select the cells from Col_A
gt; gt; lt;datagt;lt;text-to-columnsgt;
gt; gt; Select: Delimited.....[Next]
gt; gt; Delimiters: Other (enter the underscore _ )....[Next]
gt; gt; Destination: select the top cell of the range to want to contain the parsed
gt; gt; values. If your base list begins in A1, you may want the parsed values to
gt; gt; begin in B1.
gt; gt;
gt; gt; That will parse the Col_A values into Col_B and Col_C
gt; gt;
gt; gt; Does that help?
gt; gt; -----------------------
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;RJFquot; wrote:
gt; gt;
gt; gt; gt; Hello All,
gt; gt; gt;
gt; gt; gt; Can someone please help me?
gt; gt; gt;
gt; gt; gt; I am trying to split one cell into two by dividing it at the first
gt; gt; gt; underscore in the text.
gt; gt; gt; I don't know how many characters will be before or after each first
gt; gt; gt; underscore.
gt; gt; gt;
gt; gt; gt; For example, Cell A below would be divided into cells B and C with the first
gt; gt; gt; underscore being eliminated.
gt; gt; gt;
gt; gt; gt; Cell ACell BCell C
gt; gt; gt;
gt; gt; gt; 1-DEPRASST_TRANS1-DEPRASSTTRANS
gt; gt; gt; 1-ACC2-DEPR_TRANS1-ACC2-DEPRTRANS
gt; gt; gt; 2-OTHDED_SHOLD_EXP2-OTHDEDSHOLD_EXP
gt; gt; gt; 1-OTHCURRL_DEFINCOME1-OTHCURRLDEFINCOME
gt; gt; gt;
gt; gt; gt; I've been trying all kinds of formulas in cells B and C with no luck. This
gt; gt; gt; has been driving me crazy.
gt; gt; gt;
gt; gt; gt; Thank you in advance.
gt; gt; gt;
gt; gt; gt; RJF
gt; gt; gt;

Thanks Ron.

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

rjf

quot;Ron Coderrequot; wrote:

gt; OK, RJF (I didn't pay close enough attention to your examples).....
gt; Try this:
gt;
gt; For a value in A1
gt;
gt; B1: =LEFT(A1,SEARCH(quot;_quot;,A1)-1)
gt; C1: =SUBSTITUTE(A1,B1amp;quot;_quot;,quot;quot;,1)
gt;
gt; Copy those formulas down as far as needed.
gt;
gt; Is that something you can work with?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;RJFquot; wrote:
gt;
gt; gt; Thank you for your response,
gt; gt;
gt; gt; I tried that and the problem is there is sometimes more than one underscore
gt; gt; in the cell. I only need it to parse at the first underscore and keep the
gt; gt; other underscores in the text in column C. Using the text to columns parses
gt; gt; it out to column D, etc.
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try this:
gt; gt; gt;
gt; gt; gt; Select the cells from Col_A
gt; gt; gt; lt;datagt;lt;text-to-columnsgt;
gt; gt; gt; Select: Delimited.....[Next]
gt; gt; gt; Delimiters: Other (enter the underscore _ )....[Next]
gt; gt; gt; Destination: select the top cell of the range to want to contain the parsed
gt; gt; gt; values. If your base list begins in A1, you may want the parsed values to
gt; gt; gt; begin in B1.
gt; gt; gt;
gt; gt; gt; That will parse the Col_A values into Col_B and Col_C
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt; -----------------------
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;RJFquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hello All,
gt; gt; gt; gt;
gt; gt; gt; gt; Can someone please help me?
gt; gt; gt; gt;
gt; gt; gt; gt; I am trying to split one cell into two by dividing it at the first
gt; gt; gt; gt; underscore in the text.
gt; gt; gt; gt; I don't know how many characters will be before or after each first
gt; gt; gt; gt; underscore.
gt; gt; gt; gt;
gt; gt; gt; gt; For example, Cell A below would be divided into cells B and C with the first
gt; gt; gt; gt; underscore being eliminated.
gt; gt; gt; gt;
gt; gt; gt; gt; Cell ACell BCell C
gt; gt; gt; gt;
gt; gt; gt; gt; 1-DEPRASST_TRANS1-DEPRASSTTRANS
gt; gt; gt; gt; 1-ACC2-DEPR_TRANS1-ACC2-DEPRTRANS
gt; gt; gt; gt; 2-OTHDED_SHOLD_EXP2-OTHDEDSHOLD_EXP
gt; gt; gt; gt; 1-OTHCURRL_DEFINCOME1-OTHCURRLDEFINCOME
gt; gt; gt; gt;
gt; gt; gt; gt; I've been trying all kinds of formulas in cells B and C with no luck. This
gt; gt; gt; gt; has been driving me crazy.
gt; gt; gt; gt;
gt; gt; gt; gt; Thank you in advance.
gt; gt; gt; gt;
gt; gt; gt; gt; RJF
gt; gt; gt; gt;