Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RJF
 
Posts: n/a
Default Splitting 1 cell into 2 based on underscore in text

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Splitting 1 cell into 2 based on underscore in text

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" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SVC
 
Posts: n/a
Default Splitting 1 cell into 2 based on underscore in text

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.

"RJF" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RJF
 
Posts: n/a
Default Splitting 1 cell into 2 based on underscore in text

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" wrote:

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" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Splitting 1 cell into 2 based on underscore in text

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" wrote:

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" wrote:

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" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RJF
 
Posts: n/a
Default Splitting 1 cell into 2 based on underscore in text

Thanks Ron.

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

rjf

"Ron Coderre" wrote:

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" wrote:

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" wrote:

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" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Matching Text with With Certain Criteria SteveC Excel Worksheet Functions 19 April 4th 06 11:08 AM
Locate a cell, based on a criteria, then use the 'Cell' command... cdavidson Excel Discussion (Misc queries) 1 November 17th 05 07:30 PM
Text on cart based on data in a cell Ant Charts and Charting in Excel 1 August 10th 05 11:06 PM
Split Long Text Cell into Two Shorter Cells Without Splitting Word Naomi T Excel Discussion (Misc queries) 1 July 7th 05 06:49 AM
I am trying to link based on a text value instead of cell position John Links and Linking in Excel 3 December 3rd 04 07:29 PM


All times are GMT +1. The time now is 10:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"