edit and replace a text string in within a cell

[mtojek]

Greetings fellow Excel workers!

I have been searching for a solution but can't seem to find one regarding an issue that I have with a report that is being exported into Excel.

The report will export info into the first three columns in Excel, customer ID/name, and then two columns of numbers/percentages, something like:


Cell A1 Cell B1 Cell C1
ABC - ABC Co., LLC $xxxxx.xx 45%
Anderson - Anderson Technology, Inc. $xxxxx.xx 55%

I would like to edit the first columnn and remove the client ID portion only leaving the client name portion in the cell, or like this:

ABC Co., LLC $xxxx.xx 45%
Anderson Technology, Inc. $xxxx.xx 55%


This cell will always contain the character "-" which shold allow for the constant to perform the edit. I do use, and keep, the "-" in other portions of the report, so the macro can't look at the entire worksheet. I'm looking for a macro to allow me to enter in the range, or number of rows in column 1 to make this edit; the number of rows will vary from month to month report to report.

Any ideas anyone? Thanks in advance, as always, for your help!

Mark

[zbor]

Here, try this:

=TRIM(MID(A1, FIND("-", C22&"-")+1,255))

[benedictexcel]

have you tried the Left(), right() and mid() formulas?

[NBVC]

If there is only one dash in column A, you can do an Edit|Replace and enter Find What: *- (that is asterisk, dash, space) and in the Replace with, leave it blank.

Click Replace All.

Otherwise, you need a formula in another column... =TRIM(MID(A2,FIND("-",A2)+1,255)) copied down... then you can copy and paste special >> Values over column A.

[mtojek]

Thanks Zbor

benedicttexcel; I'm not familiar with those, but I will research them; thanks!

NBVC - that worked out real good; all I had to do was to go into the search/replace options ans selet the edit to only search one column since I do use and need the "-" character in other places in the same worksheet.

Thanks everyone for your help and quick response!

Mark