Moving part of text in cell to another cell Excel 2007

[Deepesh]

Hi

I want to copy certain text from the cell and paste to another cell

Example

Colum A1 Sm Unix WURPDGRSI4AB - mo chg - Sept11 inv

I want to copy WURPDGRSI4AB and paste in B1

This is dynamic value and the length of the text varies. Also the position of the text in some cells at the beginning and in some cells in the middle and end.

Can anyone help on this.

Thanks

[FDibbins]

Hi and welcome to the forum

Not pretty but it works...
=MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1,SEARCH("-",A1,1)-2-(SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1))

[Deepesh]

Thanks FDibbins, yes its working perfect

but in some colum i am getting #value error

Example


Sm Unix WUWPHWSPOSA02 Plano mo chg Nov inv

This doesn't have "-" symbol in between.. is the error due to this?

Thanks

[FDibbins]

yes it is. That formula will only work if there is a "-"

is the format always the same...text, space, text, space, what_you_want, space etc.....

[Deepesh]

No the format is not same... in some cells the data is having "-" and in some without..

If i replace all the space with "-" will the code work?

Also there are some data with the text at the beginning.. I know its bit tricky, is there any code for the below

Example
WUMPR1ASAU02A - Plano - Sm Unix - mo chg - Mar 13 inv

[FDibbins]

this will work for no "-"...
=MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)-(SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1))

How will you know when the text you want is in the beginning - or anywhere in the string - for that matter?

[Deepesh]

I'm having a spread sheet with 500 rows with the mix of data with space, "-" or with out as below. From this i need to pick the particular text(WUATSQSROSZ02 its the reference code) and paste it in the next colm. The data format is not in the proper order. The reference code will be at any place in the cell(some in the beginning, middle and end).

Sample
Sm Unix WUATSQSROSZ02 Plano mo chg Nov inv
WUYCH9AVPR02A - Char - Sm Unix - mo chg - Mar 13 inv
Sm Windows - WUKRDCBPQ3A - Char - mo chg - Oct 11 inv
Leveraged Storage - USCAWWUCZ002 - 1TB Departmental Dec 12 inv

[FDibbins]

OK then we have a problem

excel cannot "read" letters, so it cannt tell if you want to return...
Leveraged Storage - USCAWWUCZ002 - 1TB Departmental Dec 12 inv
or...
Leveraged Storage - USCAWWUCZ002 - 1TB Departmental Dec 12 inv
or
Leveraged Storage - USCAWWUCZ002 - 1TB Departmental Dec 12 inv

do you have any suggestions on how to ID what you need to extract?

[Deepesh]

Ok i have a suggestion...

Is it possible to write a code to pull the text from the cell with beginning letter starts with "US..." or "WU..." because all the text(Reference code) i required starts with US and WU.

Example
Sm Unix WUATSQSROSZ02 Plano mo chg Nov inv
WUYCH9AVPR02A - Char - Sm Unix - mo chg - Mar 13 inv
Sm Windows - WUKRDCBPQ3A - Char - mo chg - Oct 11 inv
Leveraged Storage - USCAWWUCZ002 - 1TB Departmental Dec 12 inv

[István Hirsch]

I have cheched this macro and it seems to do what you need (at least for the sample cells provided).
Press Alt+F11, go to Tools/References and check Microsoft VBSript Regular Expressions 5.5,
then change the range (now a1:a4)

Please Login or Register  to view this content.

Then select the range, go to Data/Text to Columns, choose Delimited and use colon as separator.

[Deepesh]

Thank you very much Istvan..

It works perfectly, Thanks a ton.

Regards
Deepesh