REPLACE with Array List with Wild Card

I tried that once without a solution, a workaround would be:

Please Login or Register  to view this content.

Originally Posted by dluhut

Just wondering if it's possible to modify a little bit the code to have apostrophe at the beginning? i.e '1234567 or '05ABCD0123000BLCA1020

Reason being, if the value of a particular cell of a given range is numeric...may it be whole numbers or with decimals, I would like it to appear in text...that's why the apostrophe at the beginning of the "string"

Add

Please Login or Register  to view this content.

below the array loop

Tried to add the suggested code. Although it's making the cell as text, but it's not functioning as what I want it to be.

for example, for a long whole number, it'll make it into scientific term...i.e. 1234567890 will make it to 1.234E+10 for example. Another example will be, 001234 will becomes 1234.

The reason I want an apostrophe in front of each string is to keep the format of it, may it be numbers (whole number or decimals) or alphanumeric.

So in the above example, I'll have it as '1234567890 and '001234

Hi,

could you provide a sample workbook with some data to test please.

The above code works fine for me: When I enter "1234567890" into column A and format it as Scientific Number then it will be displayed as "1,23E+09" in column A, when is code ran this number is displayed as "1234567890" in with format Text column B.

Ok, the numberformat wasn't the solution but this works for me (added a loop after copying the values to column B):

Please Login or Register  to view this content.

The code loops the cells in column B before the replacement and adds an "'" in front, so you have strings everywhere now.