ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Changin cell format and view (https://www.excelbanter.com/excel-discussion-misc-queries/239572-changin-cell-format-view.html)

sergio

Changin cell format and view
 
Hi,
I usually get part numbers from other people in the format:
006-1002126 or
008-0073140.
I would like to change it to:
0061002126 and
0080073140
I have tried formatting to custom number 0000000000 but the dashes won't get
removed and the text does not change to number.
Thanks
Sergio

Dave Peterson

Changin cell format and view
 
Number format applies to numbers.

You could insert a new column and use a formula like:

=substitute(a2,"-","")
to remove the hyphens from A2

Or you could select that range/column
Edit|replace
what: - (hyphen)
with: (leave blank)
replace all

The results will be numbers (assuming no other text in the cells). Next, you'll
want to apply the number format you want.

Sergio wrote:

Hi,
I usually get part numbers from other people in the format:
006-1002126 or
008-0073140.
I would like to change it to:
0061002126 and
0080073140
I have tried formatting to custom number 0000000000 but the dashes won't get
removed and the text does not change to number.
Thanks
Sergio


--

Dave Peterson

Luke M

Changin cell format and view
 
Try this formula to convert:

=VALUE(SUSBSTITUTE(A2,"-",""))
and then format cell as desired.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sergio" wrote:

Hi,
I usually get part numbers from other people in the format:
006-1002126 or
008-0073140.
I would like to change it to:
0061002126 and
0080073140
I have tried formatting to custom number 0000000000 but the dashes won't get
removed and the text does not change to number.
Thanks
Sergio


Gord Dibben

Changin cell format and view
 
Simply EditReplace to get rid of the dashes.

Then format to 0000000000

Note: 006-1002126 has a dash followed by a space.

You will have to deal with that also.


Gord Dibben MS Excel MVP


On Wed, 12 Aug 2009 12:32:02 -0700, Sergio
wrote:

Hi,
I usually get part numbers from other people in the format:
006-1002126 or
008-0073140.
I would like to change it to:
0061002126 and
0080073140
I have tried formatting to custom number 0000000000 but the dashes won't get
removed and the text does not change to number.
Thanks
Sergio




All times are GMT +1. The time now is 10:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com