Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Manipulating Data in an Excel Spreadsheet

Posted on 2013-11-29
Medium Priority
327 Views
I have a very large Excel table where I need to manipulate the data in one column. The data currently looks like this:

1. Reclosable Bags 2 mil 2 x 2 x 004
2. Reclosable Bags 2 mil 2 x 2 x 006
3. Reclosable Bags 4 mil 2 x 2 x .005
4. Reclosable Lock Tite Bags 3 mil 4 x 6 x 015

Each of the lines is different with the exception of the word "mil".

I need to swap the order of information so that the dimentions are first. i.e.

1. 2 x 2 x 004 Reclosable Bags 2 mil
2. 2 x 2 x 006  Reclosable Bags 2 mil
3. and so on.

Is this possible using some global command in Excel?
0
Question by:mcpilot1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 9

Accepted Solution

guswebb earned 2000 total points
ID: 39686016
Use the attached file and simply paste your values in column A. It uses the formula:
=IFERROR(CONCATENATE((RIGHT(A1,LEN(A1)-(FIND("mil ",A1,1))-3)), " ", LOWER(LEFT(A1,(FIND("mil ",A1,1))+2))),"")
file.xlsx
0

LVL 50

Expert Comment

ID: 39687096
Assuming data starts in A2 you could you use this formula in B2 to reverse the order as required:

=MID(A2&" "&A2,FIND(" mil ",A2&" mil ")+5,LEN(A2))

regards, barry
0

LVL 9

Expert Comment

ID: 39687112
Which is essentially the same result as I have previously offered, just with a slightly different method.
0

LVL 50

Expert Comment

ID: 39687142
Hi guswebb,

That's right - I'm simply proposing a marginally shorter/simpler approach

regards, barry
0

Author Closing Comment

ID: 39687267
Perfect. Worked like a charm. Thank you so much!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month9 days, 18 hours left to enroll