Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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