an apostrophe in front of the number

[theteacher9999]

Hi all,

I'm struggling with this for some time and that's why I'm here.

From the values in column A I'd like to get the result given in the example below. In column B I want to have only the number which is inside the parenthesis in column A. I would like to have an apostrophe before each number.

Do you know how to do it quickly with any formula(s) and/or VBA code?

 

[Fluff]

Hi & welcome to MrExcel.
How about
="'"&SUBSTITUTE(REPLACE(A2,1,FIND("(",A2),""),")","")

 

[gaz_chops]

Welcome to the forum, try

="'"&SUBSTITUTE(MID(A2,FIND("(",A2)+1,99),")","")

 

[theteacher9999]

Both solutions work. Thanks Fluff & gaz_chops !

 

[Eric W]

Late to the party, but also:

="'"&-MID(A2,FIND("(",A2),99)

 

[gaz_chops]

Late to the party, but also:

="'"&-MID(A2,FIND("(",A2),99)

Eric that is very nifty.

 

[theteacher9999]

Late to the party, but also:

="'"&-MID(A2,FIND("(",A2),99)

Thanks, Eric too but your solution doesn't keep 0 at the beginning.

 

[theteacher9999]

Hi Guys,

I resume our discussion becasue I have faced another issue. I used your solution, then copied and pasted the data as values and finally I got my results. The problem is the data is read by the system I use only if double click on the cells. I mean, what is in cells A2-A4 is ok but in A5-A6 is not.

Do you know how to apply the correct format to all cells in column A without clicking on each cell one by one? Maybe it can be done quickly by VBA?

 

[Fluff]

Why not get rid of the apostrophe & just use
=SUBSTITUTE(REPLACE(A2,1,FIND("(",A2),""),")","")

 

[theteacher9999]

That's a good idea. Thanks, Fluff.