formatting

Snorf · Apr 29th, 2002, 11:14 AM

I am importing data into excel. The numbers show with "cr"'s at that end because they were credits in our accounting software. Is there an easy way to remove the "CR" and put a "-" in front of the number.

Mark O'Brien · Apr 29th, 2002, 11:16 AM

Yup, CTRL+H (search and replace)

Tell it to find "CR" and replace it with "-".
Smack "replace all" button.

HTH

Snorf · Apr 29th, 2002, 11:26 AM

Thanks for your help!
Is there any way to get rid of the "cr" at the end of the number. Then put the "-" in front of the number.

Snorf · Apr 29th, 2002, 11:38 AM

I used the cntl h to get rid of the "CR"s, Then in the column after the # I put = - (inserted cell number). Then I hid the original cell.

Chris Davison · Apr 29th, 2002, 01:22 PM

one step instead of two :

=IF(RIGHT(A5,2)="CR",SUBSTITUTE(A5,"CR","")*-1,A5*1)

where A5, for example, holds your Accounting system output first cell....

then just copy down

Snorf · Apr 29th, 2002, 02:58 PM

Thanks!!!! I knew there had to be a neater way to get the job done.

Apr 29th, 2002, 11:16 AM	#2
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Yup, CTRL+H (search and replace) Tell it to find "CR" and replace it with "-". Smack "replace all" button. HTH __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Apr 29th, 2002, 01:22 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	one step instead of two : =IF(RIGHT(A5,2)="CR",SUBSTITUTE(A5,"CR","")-1,A51) where A5, for example, holds your Accounting system output first cell.... then just copy down __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 29th, 2002, 11:14 AM	#1
Snorf New Member Join Date: Apr 2002 Posts: 4	I am importing data into excel. The numbers show with "cr"'s at that end because they were credits in our accounting software. Is there an easy way to remove the "CR" and put a "-" in front of the number.

Apr 29th, 2002, 11:26 AM	#3
Snorf New Member Join Date: Apr 2002 Posts: 4	Thanks for your help! Is there any way to get rid of the "cr" at the end of the number. Then put the "-" in front of the number.

Apr 29th, 2002, 11:38 AM	#4
Snorf New Member Join Date: Apr 2002 Posts: 4	I used the cntl h to get rid of the "CR"s, Then in the column after the # I put = - (inserted cell number). Then I hid the original cell.

Apr 29th, 2002, 02:58 PM	#6
Snorf New Member Join Date: Apr 2002 Posts: 4	Thanks!!!! I knew there had to be a neater way to get the job done.