Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete part of cell contents
Hi everyone,
I have a large excel sheet with a column containing data that has a following format: ABC - Item1 BCDE - Item20 EDFGS - Item345 And I need to separate the wording of the cell without using the Text To Columns approach. Specifically, I need to delete everything before the "-" , keeping only the "Item#" part. The Text To Columns approach will create another column next to the original one, which is why I don't want to use it because their is a very complex macro in the file that uses the data in the order they are in. Adding column will alter the ranges and could cause problems. Next to this data column, there is an empty column that I can use. I can use the Left,Righ, or Mid Approach to set the value of the cell next to it, but as you can see, the number of character in the prefix as well as that of the string of interest are not constant. And excell does not understand the Wild Card "*"... I don't know what to do. I really don't want to rewrite the macro to take into account the extra column. Thanks for your help. V |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete part of cell contents
Try this, seems to state that you wanted to keep the - so i left it
=RIGHT(A1,FIND("-",A1)) "nxqviet" wrote in message ups.com... Hi everyone, I have a large excel sheet with a column containing data that has a following format: ABC - Item1 BCDE - Item20 EDFGS - Item345 And I need to separate the wording of the cell without using the Text To Columns approach. Specifically, I need to delete everything before the "-" , keeping only the "Item#" part. The Text To Columns approach will create another column next to the original one, which is why I don't want to use it because their is a very complex macro in the file that uses the data in the order they are in. Adding column will alter the ranges and could cause problems. Next to this data column, there is an empty column that I can use. I can use the Left,Righ, or Mid Approach to set the value of the cell next to it, but as you can see, the number of character in the prefix as well as that of the string of interest are not constant. And excell does not understand the Wild Card "*"... I don't know what to do. I really don't want to rewrite the macro to take into account the extra column. Thanks for your help. V |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete part of cell contents
This gets rid of the - and leaves the rest
=RIGHT(A1,FIND("-",A1,1)-1) "nxqviet" wrote in message ups.com... Hi everyone, I have a large excel sheet with a column containing data that has a following format: ABC - Item1 BCDE - Item20 EDFGS - Item345 And I need to separate the wording of the cell without using the Text To Columns approach. Specifically, I need to delete everything before the "-" , keeping only the "Item#" part. The Text To Columns approach will create another column next to the original one, which is why I don't want to use it because their is a very complex macro in the file that uses the data in the order they are in. Adding column will alter the ranges and could cause problems. Next to this data column, there is an empty column that I can use. I can use the Left,Righ, or Mid Approach to set the value of the cell next to it, but as you can see, the number of character in the prefix as well as that of the string of interest are not constant. And excell does not understand the Wild Card "*"... I don't know what to do. I really don't want to rewrite the macro to take into account the extra column. Thanks for your help. V |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete part of cell contents
John,
It worked perfectly...Thanks so much, I don't have to rewrite my macro now..thanks. Viet John Bundy wrote: This gets rid of the - and leaves the rest =RIGHT(A1,FIND("-",A1,1)-1) "nxqviet" wrote in message ups.com... Hi everyone, I have a large excel sheet with a column containing data that has a following format: ABC - Item1 BCDE - Item20 EDFGS - Item345 And I need to separate the wording of the cell without using the Text To Columns approach. Specifically, I need to delete everything before the "-" , keeping only the "Item#" part. The Text To Columns approach will create another column next to the original one, which is why I don't want to use it because their is a very complex macro in the file that uses the data in the order they are in. Adding column will alter the ranges and could cause problems. Next to this data column, there is an empty column that I can use. I can use the Left,Righ, or Mid Approach to set the value of the cell next to it, but as you can see, the number of character in the prefix as well as that of the string of interest are not constant. And excell does not understand the Wild Card "*"... I don't know what to do. I really don't want to rewrite the macro to take into account the extra column. Thanks for your help. V |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking Up Part Cell Contents | Excel Discussion (Misc queries) | |||
#N/A if cell contents not part of an array | Excel Discussion (Misc queries) | |||
Combining Cell Contents (Part 2) | Excel Discussion (Misc queries) | |||
IF statement that looks at part of the contents of a cell. | Excel Programming | |||
Can I use cell contents as part of a formula? | Excel Worksheet Functions |