Use find a replace to move a character in a cell?

[MarcDenney]

Afternoon all,

I have data that comes off the system with the minus sign at the end, eg 100-.

Is there any way of using find and replace to move the minus sign to the start of the number?

Thanks in advance

Marc

 

[doofusboy]

Assuming your data is in column A, you could put this formula in column B and copy it down as far as needed:

=SUBSTITUTE(A1,"-","")

 

[MarcDenney]

Assuming your data is in column A, you could put this formula in column B and copy it down as far as needed:

=SUBSTITUTE(A1,"-","")

Thanks, but thats just the same as a normal find and replace.

I'm looking to move the minus symbol to the start of the number.

Thanks for your help.

 

[pgc01]

Hi Marc

You can use the Text to Columns feature.

Select the column, invoke Text to Columns and in the third panel, click on the Advanced button. You are taken to the Advanced Text Import Settings where you can check the option "Trailing minus for negative numbers".

 

[MarcDenney]

Hi Marc

You can use the Text to Columns feature.

Select the column, invoke Text to Columns and in the third panel, click on the Advanced button. You are taken to the Advanced Text Import Settings where you can check the option "Trailing minus for negative numbers".

That's perfect!

Thanks

 

[doofusboy]

Thanks, but thats just the same as a normal find and replace.

I'm looking to move the minus symbol to the start of the number.

My bad for not reading your whole post. Formula could then be amendd as follows:

="-"&SUBSTITUTE(A1,"-","")