Finding the first numbers and moving to another cell

[Balmer07]

Hi,

I am trying to speed up a process I currently do, I get sent an email everyday of goods which have been processed. However I want to create a macro that carries out a few transactions for me in one go to save some time.

I get an email like below:

3-PPP-00016-GH1-50%
14-PPP-00017-GH1-50%
20-PPP-00018-GH1-50%

The first numbers in each line are the quantities processed (3, 14, 20). Basically if I copy these lines into column 'B' I want to copy the quantity into column 'C' then delete the data up until the first 0 of the 00016 code

I hope this makes sense, please let me know if you have an idea of how to do this.

Thanks
Stevie

 

[sgroath]

If you want to replace the text with the 1st 2 parts removed (e.g., 3-PPP-00016-GH1-50% with 00016-GH1-50%), you will need to wrote some VBA code. If you are alright with having a new column with the 1st 2 parts removed, this is straight forward. In the following, I am assuming your data starts in cell B2.

In C2, use the formula:
=LEFT(B2,FIND("-",B2)-1)

In D2, use the formula:
=RIGHT(B2,LEN(B2)-(FIND("-",B2)+4))

 

[Balmer07]

I should have said I am trying to right the code for it. Did not think of using 'Find' this should make life a lot easier

Thanks

 

[sgroath]

You are welcome

 

[Balmer07]

Quick question, I can't seem to get the formula to work in VBA, below is what I am inputting and it is returning with an error. Active Cell is Row 'C', and I am wanting to look into Column 'B'

ActiveCell.FormulaR1C1 = "=Left(RC[-1],FIND(" - ",RC[-1])-1)"

Any ideas?

 

[Balmer07]

I got it,

needed to input ""-"" instead of "-"

Works like a charm now