[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

delimited pipe

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
Moizsaif123
Asked:
Moizsaif123
  • 2
1 Solution
 
stergiumCommented:
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
 
helpfinderIT ConsultantCommented:
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
 
Moizsaif123Author Commented:
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
 
helpfinderIT ConsultantCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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