Pasted text string appears as number with ten decimal places - How do I convert back ?

**fallwire** · 01-18-2017, 05:23 AM

Hello,

I have copied the the following text "26:1-4-3" [talking marks not present on the website] from a website into excel but all ways of copying it have resulted in it appearing as the number 41337.0840277778 in Excel (copied as HTML, as Unicode Text, as Text - these are the only 3 options). Is there a formula in Excel that will convert this number back to the text "26:1-4-3" ??

I've viewed dozens of help files looking into ASCII, HEX2DEC but nothing seems to convert it back, would greatly appreciate some help out there.

Thanks,
Trent

**Tony Valko** · 01-18-2017, 10:20 AM

When I paste 26:1-4-3 into a cell Excel converts it to 42829.0840277778 which, when formatted, is 4/4/2017 2:01:00 AM.

How it arrives at 42829.0840277778 I have no idea.

Preformatting the destination cell as TEXT solved the issue.

**xlnitwit** · 01-18-2017, 11:05 AM

Hi,

I presume you are either using a Mac version of Excel or you have the option to use the 1904 date system set?

The text is interpreted as the time value 26:01 on 4th March 2017. Since 26:01 is over 24 hours, you add one day and 2:01 to arrive at 5th March 2017 at 2:01. In the Mac / 1904 date system, that would convert to the value 41337.0840277778, or in the 1900 system it would be 42799.0840277778. Both assume that you are using a dd/mm/yyyy date regional format, which is why Tony's value is different- the 4-3 would be interpreted as April 3 rather than 4 March with mm/dd/yyyy settings.

**Tony Valko** · 01-18-2017, 03:41 PM

One of the fringe benefits of being a MS MVP is that I have access to some of the most knowledgeable people on the planet wrt Excel.

Like most groups of associated people we have our own private forum. So I asked about this in that forum and the answer that was offered is the same as yours.

Excel thinks this: 26:1-4-3 is a date/time value.

**fallwire** · 01-19-2017, 12:32 AM

The pre-formatting sheet to text & pasting as Text seems to have worked. Yes, I gathered that it tries to recognise a date but didn't know the difference in how xlnitwit describes. Very lucky to have both of you help me with this. I had actually been using data created in Excel for Mac onto a PC Office version so was all over the place. but the text pre-formatting looks the solution so thanks again

**Tony Valko** · 01-19-2017, 12:44 AM

You're welcome. Thanks for the feedback!

Pasted text string appears as number with ten decimal places - How do I convert back ?

LinkBack

Thread Tools

Rate This Thread

Display

Pasted text string appears as number with ten decimal places - How do I convert back ?

Re: Pasted text string appears as number with ten decimal places - How do I convert back ?

Re: Pasted text string appears as number with ten decimal places - How do I convert back ?

Re: Pasted text string appears as number with ten decimal places - How do I convert back ?

Re: Pasted text string appears as number with ten decimal places - How do I convert back ?

Re: Pasted text string appears as number with ten decimal places - How do I convert back ?

Thread Information

Users Browsing this Thread

Similar Threads

Adusting decimal places based on greatest number of places in a series

[SOLVED] Converting a number with a specified number of decimal places into a string

[SOLVED] Need to limit the number of decimal places of a number in a text string

[SOLVED] copy number with 3 or more decimal places and paste the actual value as 2 decimal number

number to text but with only two decimal places

Setting the number of decimal places for a text box.

number returns only two decimal places after I change from text

Bookmarks

Bookmarks

Posting Permissions