ExcelBanter - Problem With Text & number format

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Problem With Text & number format (https://www.excelbanter.com/excel-discussion-misc-queries/259514-problem-text-number-format.html)

Problem With Text & number format

I hv this sample data in excel

019-1234567
019 1234567
019#1234567
%0191234567

My question

1) How to delete spacing, dashes, character etc leaving the number only..in
my above sample the clean data that i want to get is 0191234567

2) I tried to use replace function (to delete dash,space etc) but the
leading 0 will not be displayed.

What will be the best approach?

Thanks

Problem With Text & number format

There are several options for displaying leading zeros depending on the way
you want to use the strings.
1. Custom formatting the cells with format code "0000000000" (10 zero digits).
2. Convert to text with =TEXT(A1,"0000000000")
3. Format a range as Text and Copy/PasteSpecial-Values the result of the
above formula.

--
Regards!
Stefi

€žzyus€ ezt Ã*rta:

I hv this sample data in excel

019-1234567
019 1234567
019#1234567
%0191234567

My question

1) How to delete spacing, dashes, character etc leaving the number only..in
my above sample the clean data that i want to get is 0191234567

2) I tried to use replace function (to delete dash,space etc) but the
leading 0 will not be displayed.

What will be the best approach?

Thanks