Problem with Excel!

%3CLINGO-SUB%20id%3D%22lingo-sub-1323905%22%20slang%3D%22es-ES%22%3EProblem%20with%20Excel!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1323905%22%20slang%3D%22es-ES%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20a%20problem%20with%20Excel%3A%20I%20have%20a%20data%20base%20in%20which%20each%20data%20format%20is%20something%20like%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2280%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2280%22%20height%3D%2220%22%3E%222020-04-12T18%3A30%3A59Z-19.7840%20-%3CFONT%20color%3D%22%23800080%22%3E4.98303%3C%2FFONT%3E%230%2C000%3CFONT%20color%3D%22%23993300%22%3E36.7620%3C%2FFONT%3E%3F%3CFONT%20color%3D%22%23008000%22%3E1522.851%3C%2FFONT%3E.%20.%3CFONT%20color%3D%22%23FF6600%22%3E.%20.%20.%3C%2FFONT%3E.%20.%20%2312%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%20.%3CFONT%20color%3D%22%233366FF%22%3E19.7840%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EAnd%20so%2C%20what%20I%20have%20to%20do%20after%2C%20is%20to%20pull%20apart%20each%20number%2C%20for%20what%20I%20use%20the%20command%20of%20text%20to%20columns%2C%20with%20the%20option%20of%20chosing%20a%20separator%2C%20.%20The%20problem%20comes%20because%2C%20when%20I%20do%20this%2C%20the%20point%20of%20each%20number%20is%20changed%2C%20and%20the%20data%20I%20obtain%20is%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22720%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2280%22%20height%3D%2220%22%3E2020-04-12T18%3A30%3A59Z%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E%3CFONT%20color%3D%22%233366FF%22%3E197.840%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E%3CFONT%20color%3D%22%23800080%22%3E498.303%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E0.000%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E%3CFONT%20color%3D%22%23993300%22%3E367.620%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E%3CFONT%20color%3D%22%23008000%22%3E1.522.851%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E%3CFONT%20color%3D%22%23FF6600%22%3E261.638%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E12%20Apr%202020%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E18%3A31%3A10%20%40%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EAs%20you%20can%20see%2C%20every%20data%20is%20changed!%20And%20I%20don't%20get%20to%20understand%20why%20this%20happens.%20I%20have%20tried%20to%20change%20the%20cell%20format%2C%20but%20it%20is%20alredy%20as%20'number'.%3C%2FP%3E%3CP%3EHope%20anyone%20can%20help%20me!%20Thank%20you%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elaura%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1323905%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1328238%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Excel!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1328238%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F630379%22%20target%3D%22_blank%22%3E%40lauravazquez101%3C%2FA%3E%26nbsp%3BTook%20your%20text%20string%20and%20did%20the%20%22Text-to-columns%22%20as%20you%20described%20(delimited%20with%20%23).%20Without%20changing%20any%20of%20the%20default%20settings%20in%20the%20following%20steps%2C%20I%20obtained%20the%20correct%20results.%3C%2FP%3E%3CP%3EPlease%20try%20the%20following.%20Before%20%3CSTRONG%3Eyou%3C%2FSTRONG%3E%20press%20%22Finish%22%20in%20the%20last%20step%2C%20click%20%22Advanced%22%20and%20make%20sure%20that%20you%20have%20the%20period%20%22.%22%20as%20the%20decimal%20separator.%20Then%20it%20should%20work.%20When%20I%20changed%20it%20to%20a%20comma%20on%20my%20system%2C%20I%20obtained%20a%20similar%20result%20as%20what%20you%20showed%2C%20be%20it%20that%20I%20get%20comma's%20where%20you%20have%20periods.%20During%20text%20import%2C%20Excel%20takes%20your%20locale%20settings%20to%20interpret%20the%20numbers%2C%20unless%20you%20tell%20it%20not%20to%20do%20so%20by%20changing%20the%20advanced%20text%20import%20setting%20(picture).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-04-22%20at%2012.22.53.png%22%20style%3D%22width%3A%20289px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185850i81D254D4469E7F40%2Fimage-dimensions%2F289x229%3Fv%3D1.0%22%20width%3D%22289%22%20height%3D%22229%22%20title%3D%22Screenshot%202020-04-22%20at%2012.22.53.png%22%20alt%3D%22Screenshot%202020-04-22%20at%2012.22.53.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Good morning,

 

I'm having a problem with Excel: I have a data base in which each data format is something like: 

"2020-04-12T18:30:59Z#19.7840#4.98303#0.000#36.7620#1522.851#26.1638#12 Apr 2020#18:31:10@"

And so, what I have to do after, is to pull apart each number, for what I use the command of text to columns, with the option of chosing a separator, #. The problem comes because, when I do this, the point of each number is changed, and the data I obtain is:

2020-04-12T18:30:59Z197.840498.3030.000367.6201.522.851261.63812 Apr 202018:31:10@

As you can see, every data is changed! And I don't get to understand why this happens. I have tried to change the cell format, but it is alredy as 'number'.

Hope anyone can help me! Thank you, 

 

Laura

1 Reply
Highlighted

@lauravazquez101 Took your text string and did the "Text-to-columns" as you described (delimited with #). Without changing any of the default settings in the following steps, I obtained the correct results.

Please try the following. Before you press "Finish" in the last step, click "Advanced" and make sure that you have the period "." as the decimal separator. Then it should work. When I changed it to a comma on my system, I obtained a similar result as what you showed, be it that I get comma's where you have periods. During text import, Excel takes your locale settings to interpret the numbers, unless you tell it not to do so by changing the advanced text import setting (picture).

 

Screenshot 2020-04-22 at 12.22.53.png