Macros Excel 2007 - extracting data

Question

Im trying to extract certain data from a cell and trying to put it somewhere else within the same cell. Any suggestions?

Answer 1

You could insert new columns beside the column that contains your data and split the cell using a rule. See the function:

Data > Text to column

in the menubar, and choose 'space' as a delimiter. Then you can just join the the cells in the order you want using a concatenate function

 =CONCATENATE(E2,"-",F2)

To learn how to do this using a macro, click on the 'record a macro button' and then examine the code generated.

Answer 2

Martin,

Here is an alternative method, using MID and LEFT functions.

Just for this example let's assume that your data is in column A. If you implement the following formula in the adjacent column B:

 = MID(A1,8,100) & " " & LEFT(A1,6)

and then copy it for all the remaining cells below, then column B should have dates on the right.

I hope this helps.

Answer 3

You don't mention whether the color is always 4 letters. Assuming it is, the following formula will give the desired result, regardless of part number being 2, 3 or 4 (the input is in cell A1).

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,6)

Here are examples of input and output:

Input              Output
100906 PBO5 BLUE    PBO5 BLUE 100906
100906 PB5 BLUE  PB5 BLUE 100906
100906 P5 BLUE    P5 BLUE 100906