while importing text from web page (to be refreshed later on), the alphanumeric values (0321418U, 04776181, 0876129P, 01242109) get converted to numeric (0321418U, 4776181, 0876129P, 1242109) ie prefix zero is removed.
i want it as text only with zero prefixed.
the text have variable length and needs to be refreshed frequently
Last edited by anil.kut; 07-09-2016 at 11:05 AM.
Try this
Enter in B1 and copy down
Formula:
Please Login or Register to view this content.
v A B 1 0321418U 0321418U 2 4776181 04776181 3 0876129P 0876129P 4 1242109 01242109
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
there is always a zero in front of the valua or how do you determine if there has to be a O in front?
Show the expected result in a small exell file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
0321418U 00321418U
4776181 04776181
0876129P 00876129P
1242109 01242109
1826v 01826v
b1="0"&A1
this may not work as i have to use vlookup with column B, and the real data to which it is to be matched is real text, not just appears like text.
why can't we get alphanumeric imported as text (alphanumeric) in excel
You get 2 answers, so it is usefull to who you are replying.
I don't know to whom you replying to. If you referring to post #2 the TEXT function actually writes text values to the cell and it is not just formatting.
Dear oeldere,
both answer give the same result, number appears to be prefixed with zero but not in reality as when i used vlookup error message appeared.
moreover the length of text is variable too and data are in thousands, how to deal with it
Last edited by anil.kut; 07-09-2016 at 11:45 AM.
Oeldere sir, you are right, vlookup applies correctly, but how to deal with variable length.
sir, you are right, vlookup applies correctly, but how to deal with variable length.
Alkey sir, you are right, vlookup applies correctly, but how to deal with variable length.
Alkey sir,
the data is to be refreshed frequently and linked with other sheet with expandable no. of records.
It will be better if we can get alphanumeric imported as text (alphanumeric) in excel, without adding any more column or so.
anyhow this will suffice but i am not satisfied.
thanks
Depending on what you mean exactly by "importing", I've had a lot of experience with this problem dealing with importing lists of alphanumeric serial numbers and UPC numbers with leading zeros from the web into excel spreadsheets, and it is definitely frustrating to say the least... The only viable solutions I found were VBA based controls of incoming text and simultaneous control of user input, mostly limiting the user's ability to access paste functionality. Excel deals with leading zeros badly. Even when the cells receiving the incoming data are formatted as text, it can still convert numbers with leading zeros into numerical values instead of their original layout, especially when the end user isn't familiar with excel's special pasting abilities. Depending on your scenario, neither of these solutions will function all the time. For example, if you have varying string lengths and some numbers with more than one leading zero (which your example does), you will end up with incorrect data with one or both of these options. So, any real solution is completely scenario based; i.e., very specific to your situation and your workbook(s). If you're trying to copy/paste data from the web it can be tricky, but doable with VBA. If you're importing directly somehow, please tell me more...
If I helped, please click on Add Reputation.
Request in 3
Show the expected result in a small exell file.
the answer in #4 is a solution with a variable length.Oeldere sir, you are right, vlookup applies correctly, but how to deal with variable length.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks