• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fix text input convert to date!!

C

Cuncon

New Member
Hi, Please help to fix this: I have a list of registration having mixed between numbers and text but excel (2007) automatically converted to date. I dont know how to convert it back to original text.

For example:

Name ID ---> Output

David C march27 27-Mar

Adam A 41008 (same number)

Ray R apr11 11-April

Cooper M 41015 41015

Ryan C may07 7-May

Darevic L jun2012 Jun-12

Yulinana B 7december 7-Dec


And it is very long list which I could not know original input of those names. So how could I use formulas to convert it back? Or how to get the original input?


Thanks in advance
 
so you have their names in Col.A and the ID's in Col.B? yes?

the issue you're having is that the ID # is automatically getting formated in the 'dd-mmm' format--yes? but you want it to display the actual ID# rather than 27-Mar, yes?


if im understanding this correctly...

If the ID# was entered as an actual number and Excel automatically converted it to the "27-Mar" format, then you should be able to simply set the number formatting back to general.
 
It is a yes to all your questions. I already tried those tips:

1. Set to General: it would be displayed "40991", which is not actual ID

2. Set it to Custom format: "mmmd", which is correct for only "march27" nut not correct for "jun2012" and "7december". And please be noted that I have a very long list of IDs, which I can find out which is an actual ID since it was automatically converted to date. I need to check one by one in a bunch of those members.

3. I tried to convert it to text, but it displays numbers....

Is there other ways I can try?
 
Hi, Cuncon!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Fix%20text%20input%20convert%20to%20date%21%21%20%28for%20Cuncon%20at%20chandoo.org%29.xlsx


Col. A: Name


B: ID


C: Output (your data)


D: =SI(NO(ESNUMERO(B2));EXTRAE(B2;COINCIDIR(VERDADERO;ESNUMERO(1*EXTRAE(B2;RESIDUO(FILA($1:$10);10);1));0);CONTAR(1*EXTRAE(B2;RESIDUO(FILA($1:$10);10);1)));"") -----> in english: =IF(NOT(ISNUMBER(B2)),MID(B2,MATCH(TRUE,ISNUMBER(1*MID(B2,MOD(ROW($1:$10),10),1)),0),COUNT(1*MID(B2,MOD(ROW($1:$10),10),1))),"")


E: =SI(NO(ESNUMERO(B2));SI(HALLAR(D2;B2)=1;DERECHA(B2;LARGO(B2)-LARGO(D2));IZQUIERDA(B2;HALLAR(D2;B2)-1));"") -----> in english: =IF(NOT(ISNUMBER(B2)),IF(SEARCH(D2,B2)=1,RIGHT(B2,LEN(B2)-LEN(D2)),LEFT(B2,SEARCH(D2,B2)-1)),"")


F: =SI(NO(ESNUMERO(B2));FECHANUMERO(SI(VALOR(D2)<=31;D2;1)&"/"&IZQUIERDA(E2;3)&"/"&SI(VALOR(D2)<=31;AÑO(HOY());D2));B2) -----> in english: =IF(NOT(ISNUMBER(B2)),DATEVALUE(IF(VALUE(D2)<=31,D2,1)&"/"&LEFT(E2,3)&"/"&IF(VALUE(D2)<=31,YEAR(TODAY()),D2)),B2)


Formulas at column D are array formulas, so remember that should be entered with Ctrl-Shift-Enter instead of just Enter.


Adapted from source: http://office.microsoft.com/en-us/excel-help/extracting-numbers-from-alphanumeric-strings-HA001154901.aspx


Just advise if any issue.


Regards!
 
Hi, thank you all for your advice. And gracias SirJB7! But I think I did not make my concern clearly. Let me elaborate it again: I have a list of IDs containing alphanumeric entries and only numberic entries which are unique (date is an invalid ID). The problem is excel automatically converted a bunch of them into dates. I need to check what actual IDs are (cuz the display is in short date format)

1. I want to use formulas to get the correct IDs, which are not date displays or I have no ideas the actual IDs till I saved the file again in notepad *.txt format and Ctrl + F to find names...

It sounds like lots of manual working... :(
 
Hi ,


From what I have read , your 4 samples are as follows :

[pre]
Code: 
David C      march27     27-Mar
Ray R         apr11        11-April
Ryan C       may07        7-May
Yulinana B  7december 7-Dec
[/pre]
As you can see , the excel format is now d-mmm in 3 cases , and d-mmmm in one case ; however , the original text follows 3 formats viz. march27 , may07 and 7december. Given 1 format to start with , I don't think anyone can give you back the original text in their original formats.


If you have the original input file , then before importing it into Excel , set the format for the cells to Text , and then do the import ; while importing , again select the format as Text for each of the fields , so that Excel does not try to convert them based on their content.


Narayan
 
Hi, thanks you all for helping.


Hi NARAYANK991, the file I got is in .csv format which was generated from website. I tried to set format for the column A (IDs), and text to columns function was activated. But there were few lines falling on the wrong colums... Anyway, I think you are right. The original input sounds good.


Love this forum,

Cheers,
 
Hi, Cuncon!

Now that I read again more carefully your original post, you're right, you were looking and asked for another thing. I apologize for that.

Regards!
 
You must log in or register to reply here.
Back
Top