| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching Text with With Certain Criteria | Excel Worksheet Functions | |||
| Locate a cell, based on a criteria, then use the 'Cell' command... | Excel Discussion (Misc queries) | |||
| Text on cart based on data in a cell | Charts and Charting in Excel | |||
| Split Long Text Cell into Two Shorter Cells Without Splitting Word | Excel Discussion (Misc queries) | |||
| I am trying to link based on a text value instead of cell position | Links and Linking in Excel | |||
Linear Mode
