Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

EDIT

I have several text strings in ONE cell (a date, a part number and a color). The format looks like this 100906 PBO5 BLUE. The date is always the same number of characters but the part number could be 2, 3 or 4 characters.

I want to move the date to the position after the color so it looks like this PBO5 BLUE 100906. I have over 1,000 records so I don’t want to do this manually.

share|improve this question
1  
Can you post a specific example of what you want to do? –  JNK Sep 7 '10 at 19:34
    
I have several text strings in ONE cell (a date, a part number and a color). The format looks like this 100906 PBO5 BLUE. I want to move the date to the position after the color so it looks like this PBO5 BLUE 100906. I have over 1,000 records so I don’t want to do this manually. Any help would be appreciated. –  Martin Sep 7 '10 at 20:34
    
Is the date and part number always that number of digits? –  JNK Sep 7 '10 at 20:51
    
The date is always the same the part number could be 2, 3 or 4 characters. –  Martin Sep 8 '10 at 12:50

3 Answers 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
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.