How to delete last character from a text string using VBA??

[jacksongf]

Hey everyone, I need to write code that will delete the last character of a text string if there is a dash at the end. If there is no dash, I want it to simply pass over it and not do anything to it. The range is C2 to C102, and the numbers very from hundreds to millions.

(I'm importing data from SAP and need to remove the dashes from the end of the numbers, since it's making it text and is therefore incalculable. I tried using a LEFT/FIND formula, but it is still only recognized as text due to there being commas in the text string).

Thanks!

Jackson

 

[Rick Rothstein]

Hey everyone, I need to write code that will delete the last character of a text string if there is a dash at the end. If there is no dash, I want it to simply pass over it and not do anything to it. The range is C2 to C102, and the numbers very from hundreds to millions.

(I'm importing data from SAP and need to remove the dashes from the end of the numbers, since it's making it text and is therefore incalculable. I tried using a LEFT/FIND formula, but it is still only recognized as text due to there being commas in the text string).

Before you simply remove the dash, why is it there? My guess is that it is signaling the number is negative. If that is the case, you would not want to remove it, you would want to, in effect, move it to in front of the number instead. Can you confirm that you really just want to remove it or would you actually want it moved to the front of the number?

 

[jacksongf]

It's not there to be negative, it's only there as per the format from SAP. I would like it to be removed from the text string.

 

[Rick Rothstein]

It's not there to be negative, it's only there as per the format from SAP. I would like it to be removed from the text string.

Since I am guessing that, if there, it will be the only dash in the cell, simply select the column, press CTRL+H to bring up the Replace dialog box, put a single dash in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure the "Match entire cell contents" checkbox is not checked, then click the "Replace All" button.

 

[jacksongf]

That worked perfectly! Can't believe I didn't think of that, such a simple solution.

Thank you very much Rick.

Jackson