Solved

Manipulating Data in an Excel Spreadsheet

Posted on 2013-11-29
5
292 Views
Last Modified: 2013-11-30
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
Comment
Question by:mcpilot1
  • 2
  • 2
5 Comments
 
LVL 9

Accepted Solution

by:
guswebb earned 500 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

by:barry houdini
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

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

Expert Comment

by:barry houdini
ID: 39687142
Hi guswebb,

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

regards, barry
0
 

Author Closing Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question