• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Move to the right two cells based on previous cell

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
model_un
Asked:
model_un
  • 5
  • 3
1 Solution
 
mark_harris231Commented:
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
 
model_unAuthor Commented:
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
 
model_unAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mark_harris231Commented:
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
 
model_unAuthor Commented:
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
 
model_unAuthor Commented:
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
 
model_unAuthor Commented:
Resolved. Had to play around, but with some copying and pasting took care of the issue.

Thanks again!

FF
0
 
mark_harris231Commented:
Happy to help and glad you were able to bridge the gaps.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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