Solved

Move to the right two cells based on previous cell

Posted on 2013-06-18
8
299 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

617 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