[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Manipulating Data in an Excel Spreadsheet

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
mcpilot1
Asked:
mcpilot1
  • 2
  • 2
1 Solution
 
guswebbCommented:
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
 
barry houdiniCommented:
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
 
guswebbCommented:
Which is essentially the same result as I have previously offered, just with a slightly different method.
0
 
barry houdiniCommented:
Hi guswebb,

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

regards, barry
0
 
mcpilot1Author Commented:
Perfect. Worked like a charm. Thank you so much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now