My data comes in several forms and I need to convert it to a unified form (VBA)

[mick0005]

Hi all,


I have SKUs that comes from several sources. The problem with these sources is that they aren't consistent. All SKUs need to be 8 digits with no symbols in it and no spaces and no blanks at the beginning or end.


I have several formulas I have been using to convert this data manually every time I import it with some helper columns, but this is a huge time suck.


I'd like to be able to just select the range of data I need converted and then it would convert in place. Here are the issues I have and would need converted:


Excel 2010

	E	F	G
8	Problem	Input	Output
9	hyphen in the middle	12345-678	12345678
10	only 6 numbers; needs 2 zeroes added at the beginning	123456	00123456
11	only 7 numbers; needs 1 zero added at the beginning	0123456	00123456
12	requires no change	12345678	12345678

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Removing "-"​

 

[c2shar]

Formula in G9 would be: =IFERROR(LEFT(F9,SEARCH("-",F9,1)-1)&RIGHT(F9,LEN(F9)-SEARCH("-",F9,1)),F9) and drag till end.

Set cell formatting for column G to "Custom" and put 8 zeros in Type [ Format Cells -> Custom -> Type -> 00000000]

Hope this solves your problem.

Regards
Tushar

 

[mick0005]

Formula in G9 would be: =IFERROR(LEFT(F9,SEARCH("-",F9,1)-1)&RIGHT(F9,LEN(F9)-SEARCH("-",F9,1)),F9) and drag till end.

Set cell formatting for column G to "Custom" and put 8 zeros in Type [ Format Cells -> Custom -> Type -> 00000000]

Hope this solves your problem.

Regards
Tushar

Hi Tushar,

Thank you for your suggestion. In fact, this is more or less the formula that I am using at the moment to accomplish this. I am wondering whether there is some VBA that can do this for me. I am trying to shave off some precious minutes or even seconds from this process because it is something that has to be done very frequently. Automating this will save lots of time in the long haul.

I think I could probably record a macro that would do this for me by adding a helper column, putting in the formula, and then copying and pasting this over the original column as values and deleting the helper column, but I am wondering if there is code that could do it all in place.

Thanks to anyone that can help.