Copy part of cell (in Edit mode), to a different cell in a loop macro.
Hello,
Looking for idea to write a macro replacing the first three characters currently sitting in the ERP column.
PartId Number ERP
Oct-35 414825 01-10-3035
Oct-60 423957 01-10-3060
Oct-92 508694 01-10-3292
Oct-95 509789 03-05-2018
Oct-12 515998 01-10-3312
Oct-43 527320 01-10-3343
The above data comes from our ERP as csv file and shows date in the PartID columns on 6,600 parts out of 30K. It is formatted as date and the Number column shows what is the value for the date. The data in cell however looks like the one in column ERP.
When you click F2 remove the first 3 characters, enter ' to mark it a text and enter key, it properly shows part ID as 10-3035.
I tried to write a macro replacing three rows to use in a loop, but it enters hard coded Part ID.
Then I tried to press F2 - Copy the right seven characters, but it enter hard coded Part ID again.
What I need it to copy and paste the actual changing part ID.
Any idea how can I do that?
The right() function doesn't work because it the number.
Your help is greatly appreciated.
Thank you.
Looking for idea to write a macro replacing the first three characters currently sitting in the ERP column.
PartId Number ERP
Oct-35 414825 01-10-3035
Oct-60 423957 01-10-3060
Oct-92 508694 01-10-3292
Oct-95 509789 03-05-2018
Oct-12 515998 01-10-3312
Oct-43 527320 01-10-3343
The above data comes from our ERP as csv file and shows date in the PartID columns on 6,600 parts out of 30K. It is formatted as date and the Number column shows what is the value for the date. The data in cell however looks like the one in column ERP.
When you click F2 remove the first 3 characters, enter ' to mark it a text and enter key, it properly shows part ID as 10-3035.
I tried to write a macro replacing three rows to use in a loop, but it enters hard coded Part ID.
Then I tried to press F2 - Copy the right seven characters, but it enter hard coded Part ID again.
What I need it to copy and paste the actual changing part ID.
Any idea how can I do that?
The right() function doesn't work because it the number.
Your help is greatly appreciated.
Thank you.
ASKER
Will do.
Removing rows which are not needed. It is pretty big.
Removing rows which are not needed. It is pretty big.
just a small part like 1000 row is enough
gowflow
gowflow
ASKER
It is not responding. I don't want to kill it, yet.
What is not responding ?
gowflow
gowflow
ASKER
There is it is.
Recording a macro gives this:
Range("U3").Select
ActiveCell.FormulaR1C1 = "'10-9877"
Range("U2").Select
ActiveCell.FormulaR1C1 = "'10-3060"
Range("U3").Select
Recording macro with F2 to get the value of 7 characters from the right gives this:
Range("L2").Select
ActiveCell.FormulaR1C1 = "10/1/3060"
Range("V2").Select
ActiveCell.FormulaR1C1 = "'10-3060"
Range("L2:L3").Select
Dates.xlsx
Recording a macro gives this:
Range("U3").Select
ActiveCell.FormulaR1C1 = "'10-9877"
Range("U2").Select
ActiveCell.FormulaR1C1 = "'10-3060"
Range("U3").Select
Recording macro with F2 to get the value of 7 characters from the right gives this:
Range("L2").Select
ActiveCell.FormulaR1C1 = "10/1/3060"
Range("V2").Select
ActiveCell.FormulaR1C1 = "'10-3060"
Range("L2:L3").Select
Dates.xlsx
obviously the data here is messed coz badly imported !!!
Please post the csv file
OR
Explain in the file you posted what you want. There are only 5 rows with ERP …. it is not clear what you want I would have expected to see a column called ERP with the data there !!!
gowflow
Please post the csv file
OR
Explain in the file you posted what you want. There are only 5 rows with ERP …. it is not clear what you want I would have expected to see a column called ERP with the data there !!!
gowflow
ASKER
This is how it comes. It is correct. Column L has the data which should be the Part ID. In this picture you can see what's in the cell.
ASKER
This is how it comes to csv file from ERP.
ASKER
I need to go through each cell in column L and pull all characters after the first dash as part ID, as text.
That is not how it comes on my machine it comes as a date 10/1/3374 etc...
So what you want is to keep the last 4 digits ? and remove the 10/1/ ??? is that what you want ?
Your part number is 4 digits ?
gowflow
So what you want is to keep the last 4 digits ? and remove the 10/1/ ??? is that what you want ?
Your part number is 4 digits ?
gowflow
ASKER
Because of your regional date format in Windows it comes like that. It is just formatting.
I want 10-3035 as the part number - all after the first dash. It has to be text, that's why it needs ' in front. Otherwise it becomes number. Excel subtracts 3035 from 10.
I want 10-3035 as the part number - all after the first dash. It has to be text, that's why it needs ' in front. Otherwise it becomes number. Excel subtracts 3035 from 10.
ok fine what is your date format
dd//mm/yyyy
or
mm/dd/yyyy
Will get there no worries !!!
gowflow
dd//mm/yyyy
or
mm/dd/yyyy
Will get there no worries !!!
gowflow
ASKER
short is dd-mm-yy
long is dd-mmm-yyyy
long is dd-mmm-yyyy
Thought so !!!
I am mm/dd/yyyy this is why I was getting the 10 first the 1 then the supposing year … :)
so you want 10-xxxx
right ?
gowflow
I am mm/dd/yyyy this is why I was getting the 10 first the 1 then the supposing year … :)
so you want 10-xxxx
right ?
gowflow
ASKER
Yes.
It could be 40-xxxx. It all depends if Excel treated the csv imported cell as date which I show in column R.
It could be 40-xxxx. It all depends if Excel treated the csv imported cell as date which I show in column R.
One last thing you want me to copy the new part number in Col T PartID ?
like it will hold 10-xxxx ?
gowflow
like it will hold 10-xxxx ?
gowflow
yes but the file you posted they are all 10- !!!
maybe in your whole data there is 40 but then I doubt !!!
I don;;t understand your col R formula !!! You got me lost here.
Best is for you to post the csv raw not excel as .txt then I built a macro to import it to Excel !!! if you use the Excel translator then we have a problem. Try saving your csv as .txt and then post it here.
gowflow
maybe in your whole data there is 40 but then I doubt !!!
I don;;t understand your col R formula !!! You got me lost here.
Best is for you to post the csv raw not excel as .txt then I built a macro to import it to Excel !!! if you use the Excel translator then we have a problem. Try saving your csv as .txt and then post it here.
gowflow
ASKER
The formula indicates if the format there is Date.
Change the format to anything else and it should change to 2. You might need to press F9 to recalculate because I maybe have manual calculation.
In my macro i wanted to have if this cell is one copy part of the other cell to a new location as part id.
Change the format to anything else and it should change to 2. You might need to press F9 to recalculate because I maybe have manual calculation.
In my macro i wanted to have if this cell is one copy part of the other cell to a new location as part id.
ASKER
and if it is not 1 copy the whole cell. (actual part id).
It would create a column of Part IDs only.
It would create a column of Part IDs only.
ASKER
Sorry.
To answer your question about posting the file as csv. This is automatically generated report from ERP. It starts Excel as csv file. No control over it.
To answer your question about posting the file as csv. This is automatically generated report from ERP. It starts Excel as csv file. No control over it.
ok fine now it is more clear. The file I have here is all 1 so I assume the file you have 2 also ? Is it possible for you to post the whole file so I make sure the macro works fine and no fiddling around ?
I hate to make work that I cannot test as at the end it is total waste of time to have to go back and forth
gowflow
I hate to make work that I cannot test as at the end it is total waste of time to have to go back and forth
gowflow
ASKER
The file is 17MB. Is that OK?
194,213 rows.
194,213 rows.
ASKER
There it is.
WIP.xlsm
WIP.xlsm
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
OMG.
In my mind I had to have a vba, which I tried to write and it did not work.
This is a perfect solution!
Don't know what to tell you.
Thank you.!
In my mind I had to have a vba, which I tried to write and it did not work.
This is a perfect solution!
Don't know what to tell you.
Thank you.!
I also thought of VBA as this is my job but then I said let me try this …
glad it worked for you
gowflow
glad it worked for you
gowflow
ASKER
And I am ERP guy blindsided with learning vba in Excel.
Got to exited I guess.
It worked perfectly.
I am just kicking myself in the but that I did not finish it as I am the formula guy.
Thank you.
Good day.
Got to exited I guess.
It worked perfectly.
I am just kicking myself in the but that I did not finish it as I am the formula guy.
Thank you.
Good day.
Well good to know and glad I helped you. Good day to you too. Do not hesitate to message me when you need any help.
gowflow
gowflow
gowflow