May I please obtain some VBA assistance?

[Davealot]

Greetings,
I am attempting to obtain some assistance on a UDF. I have an issue in which I pull part numbers from a customer portal to create pick lists, Pull sheets, Build sheets, etc. I have several issues with doing this, as the format that the numbers are in, often are confused as "Scientific" and it's a pain in the back end to work with. Additionally, we have to scan the labels into a database that I've built for shipment, but we track these parts separately in a database. We use QAD as our main shipment program, but with this specific customer they prefer to use the ASN system in Edict. Regardless, when I pull the parts from the portal, a simple copy and past into excel, parts such as "879310e071" always come through in scientific designation, causing me to have to scroll through rows and rows of data, comparing to open orders to try and figure out what the part number should be, store it as text...you know the routine. Additionally when I load these parts from Access, into QAD the part numbers are different. We have a part number such as "879100e011a0" which is the customer part number, it's stored in QAD as "87910-0E011-A0". Also, this customer uses check digits, so what's scanned will come through as "879100e011a0%f". I would like to develop a UDF that will take what's scanned "879100e011a0&f" and transpose that to "87910-0e011-a0" to allow for easy CIMloads into QAD. Typically I'll just find a code, copy and paste, work through it and think it out until I develop what I need, but I'm new to VBA in this regard and can't seem to locate a UDF that can read both numbers and letters. The part numbers that are scanned always follow the format posted, meaning that there will never be any deviation from the first 12 characters scanned each time. Can anyone offer assistance? Sorry for text wall, I know usually the problem begins with not enough information. Regards!

 

[Luke M]

Hi Davealot, and welcome to the forum!

Sounds like quite the challenge. Sounds like the first step might be to make sure that the cells/macro/etc. reading the information is formated as text, to first make sure you aren't messing up with scientific notation.

For the latter question, about converting the strings, I think this small UDF should help you get started

Function PartChange(strNum As String) As String
'What item will we replace?
Const checkNum As String = "&f"

'insert our hypens in correct spot
'Uses the Left and Mid functions, same as regular worksheet functions
PartChange = Left(strNum, 5) & "-" & Mid(strNum, 6, 5) & "-" & Mid(strNum, 11, 2)

End Function

How it looks in sheet:

 

[Davealot]

You, my friend, are the man! Regarding the dropping of parts from the portal, Even when I format the column to "text" from scientific, it still drops the parts in as scientific. I am fully aware that I am not as skillful as most on this forum, but I've tried everything my weak mind knows to do and cannot come to a conclusion!

 

[Luke M]

Is there any way to concatenate text onto item as it gets scanned in? I don't fully understand all the systems you mentioned in main post, so I'm just guessing. Somehow, we need to either format the cells to text and have the info being sent in stay as text-to-text. If the system that's dumping it into XL is the culprit, you'll have to go further up the process.

 

[Davealot]

My 2 year old got sick today, otherwise I would have replied sooner, my apologies. Skip over the systems and think of it like this, I log in online, there is a huge list of part numbers with quantities to ship. Highlight, Copy, Paste into Excel, numbers get goofy. The lady across from me formats the first cell in her spreadsheet as "Text", pastes to match formatting. This works on some part numbers but not all, and the quantities also come through as text, which doesn't help me. I've worked with my IT dude at work, but he can't seem to figure it out either. It's driving me bananas because I'm sure there is a way, but I can't figure it out. Perhaps it's my geekiness but things like that are impossible for me to just put down and leave alone.

 

[Chihiro]

Can you use Get External Data->"From Web"?


Or copy, then right click on Cell. Paste Special -> "Unicode" or "Text" option.

Another one is to paste data onto note pad, save as text and import, using "Text" as column type.

 

[Davealot]

Chihiro, Thanks for the response. I've messed with the "From Web" option quickly, I'll admit I didn't give it due diligence. I will attempt Monday morning and respond back to you. I have not used the paste into note pad method either, thank you for the idea as I'll admit I didn't even consider that option. Thank you kindly!

 

[Davealot]

The drop to notepad first, and importing into excel worked like a charm, and attaching the code above into the spreadsheet has made this all work like a charm. Many thanks to the ninjas above!