Solved

delimited pipe

Posted on 2014-03-31
4
114 Views
Last Modified: 2014-04-03
This Excel sheet contains "Ship to Address" which has content delimited by pipes so I need to have a formula to split those content by pipes or comma and place them in "Ship to Address1","Ship to Address2","Ship to Address3"
customer-master.xls
0
Comment
Question by:Moizsaif123
  • 2
4 Comments
 
LVL 13

Expert Comment

by:stergium
ID: 39968513
Hello
You can go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.
hope that helps
0
 
LVL 19

Expert Comment

by:helpfinder
ID: 39968518
if you mean you have to have each line in D column in separate column then you can use Replace feature (Ctrl+H) to replace sign which makes new line in the cell with some other sign, e.g. pipe (|) where formula is:
=SUBSTITUTE(E2,CHAR(10),"|")
then you will have whole text in the one line so you can use Test-to-columns feature with the pipe as a delimiter
0
 
LVL 4

Author Comment

by:Moizsaif123
ID: 39968538
say i have this formula below to delimit according to comma then how do i apply to all columns instead of just D2 since there are 400 rows in column D
=RIGHT(D2,LEN(D2)-FIND(",",D2,1))
0
 
LVL 19

Accepted Solution

by:
helpfinder earned 500 total points
ID: 39968602
just copy it down. if this formula is in E2 then just go with your cursor to the cell right botom corner, since you have this coursor:
cursorand then double click or just press the left mouse button and drag down
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search Workbook, then move rows to new sheet 13 31
location range 4 22
FormulaArray VBA Issue 6 17
Excel 2010 - Populate a formula in multiple cells 6 21
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now