Solved

Move to the right two cells based on previous cell

Posted on 2013-06-18
8
296 Views
Last Modified: 2013-06-19
Hello all. I need to format an address list in a jiffy.

The data is in EXCEL. But will be incorporated into a WORD document.

The final text will be like this:

Argentina
Argencert S.A. (ARG)
Scope:     Crop, livestock, wild crop, handling
Address: Bernardo de Irigoyen 972, 4 "B"
               Ciudad Autonoma de Buenos Aires
               Argentina
Contact:  Laura Cecilia Montenegro
Phone:    54 11 4363 0033
Email:     info@argencert.com.ar
Website: http://www.argencert.com.ar

The problem is that the second and third line of the address in the column with the "Address:"

How can I tell EXCEL to move the second and third line of the address over one cell to the right. So that the "content" is in the second column?

Many thanks!

Fernando
organic-sample.xlsx
0
Comment
Question by:model_un
[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
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 10

Expert Comment

by:mark_harris231
ID: 39257605
Fernando - attached is one example that does some IF/AND comparisons in Column C.  The formula is copied down the sheet.  Once complete, you can use Copy/Paste Special...Values to move Column C in to Column B.

(NOTE: There is an inconsistency in the data format on row 11 of your sample.  In all other instances, there is a blank line between the Website: and the name of the next business.  My formula is dependent on this blank line.  If a line is added before Food Safety S.A. (FS), my formula works.  Was this an entry error or is it possible that this line may be missing in the full data set?)
Copy-of-organic-sample.xlsx
0
 

Author Comment

by:model_un
ID: 39257686
Thank you Mark.

So... as to the inconsistency.

The problem is that for SOME countries you have multiple entries. In those cases you DO NOT HAVE spaces.

What would be the easiest solution? What if I first go in and add a "filler" text. Something that I can easly remove later???

FF
0
 

Author Comment

by:model_un
ID: 39257718
Sorry. Mark. Spoke too soon!!! The spaces are in fact between ALL entries. The missing one in 11 is human error.

But I can fix that with the  "filler text".

The larger issue... is how to do I now get a column that is ONLY descriptives? (scope, contact, email, etc.)
0
Technology Partners: 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!

 
LVL 10

Accepted Solution

by:
mark_harris231 earned 500 total points
ID: 39257826
See Sheet2.  I used Copy/Paste Special...Values from Columns C, D & E on Sheet1.  I wasn't sure if you wanted the business names to stand apart from the labels.  If not, you could add another column and use CONCATENATE(A1,B1) copied down.  This will merge columns A&B into a single column.
Copy-of-organic-sample.xlsx
0
 

Author Closing Comment

by:model_un
ID: 39259160
Perfect! I can definately take this and run with it!

Million thanks! I was on the right track with the "IF" function, but could take the next step in cross referencing with the blank cell.

Thanks again,

Fernando
0
 

Author Comment

by:model_un
ID: 39259302
Mark - question... do I need to do the functions in a particular order?

When I add the additional information that needs to be parsed out and then copy down the three functions, I do not get the parsing that occurs in the sample text. I get the placement of a "0" and no movement of the company and country name.

My thought was to add the additional information to your sample and copy down.

There seems to be some hidden number formating that is creating the "0" in blanks cells. When I copy over your "blank" cells the formulas seem to work.

See attached.

Thanks again!

Fernando
Copy-of-organic-sample2.xlsx
0
 

Author Comment

by:model_un
ID: 39259550
Resolved. Had to play around, but with some copying and pasting took care of the issue.

Thanks again!

FF
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 39259867
Happy to help and glad you were able to bridge the gaps.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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