Trim formula

**stemcell1** · 10-23-2008, 10:53 AM

I have been using the following formula's to trim some information that's separated by a colon, such as Pioneer Clinic:Dave McAfee...so that I have a column with Pioneer Clinic and a column with Dave McAfee:

=LEFT(G5,FIND(":",G5)-1)
=TRIM(RIGHT(G5,LEN(G5)-FIND(":",G5)))

However, now some of my data is separated by two colons!
AVM:Pioneer Clinic:Dave McAfee

Can anyone show me the formula for separating this so I still have two columns of Pioneer Clinic and Dave McAfee?

Thanks!

**Ron Coderre** · 10-23-2008, 11:08 AM

With
G5 containing a text phrase containing 1 or 2 colons.

These formula parse that phrase into two sections.
The part AFTER the last colon

I5: =MID(G5,FIND(CHAR(7),SUBSTITUTE(G5,":",CHAR(7),
LEN(G5)-LEN(SUBSTITUTE(G5,":",""))))+1,255)

The part up to the last colon

H5: =SUBSTITUTE(G5,":"&I5,"")

Note: CHAR(7) is the ASCII code for sounding a PC Bell

Is that something you can work with?

**stemcell1** · 10-23-2008, 11:17 AM

Thanks Ron! That was very helpful!!!

On the second formula, can you tell me how to get to the middle portion of information:

ie AVM:Pioneer Clinic:Dave McAfee

The second formula you posted gave me AVM:Pioneer Clinic, but I need to show just Pioneer Clinic and get rid of the AVM. I've played with the MID and TRIM and LEFT but haven't been able to come up with a formula that works.

Thanks so much! It's so nice when people respond with actual suggestions instead of just telling me to go to the help files...obviously I already tried that and couldn't figure it out!!

**martindwilson** · 10-23-2008, 12:23 PM

well
for first part

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))>1,MID(SUBSTITUTE(A1,":","/",1),FIND("/",SUBSTITUTE(A1,":","/",1))+1,FIND(":",SUBSTITUTE(A1,":","/",1))-FIND("/",SUBSTITUTE(A1,":","/",1))-1),LEFT(A1,FIND(":",A1)-1))

for second part

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))>1,TRIM(RIGHT(SUBSTITUTE(A1,":","/",1),LEN(SUBSTITUTE(A1,":","/",1))-FIND(":",SUBSTITUTE(A1,":","/",1)))),TRIM(RIGHT(A1,LEN(A1)-FIND(":",A1))))

where a1 contains your original text

**Ron Coderre** · 10-23-2008, 12:39 PM

I didn't realize that you wanted all 3 sections if there are 2 colons

With
G5: (the text to parse....eg AVM:Pioneer Clinic:Dave McAfee)

This formula returns the part after the LAST colon

J5: =MID(G5,FIND(CHAR(8),SUBSTITUTE(G5,":",CHAR(8),
LEN(G5)-LEN(SUBSTITUTE(G5,":",""))))+1,255)

This formula returns the AVM part....only if there are 2 colons
otherwise it is blank ("")

H5: =IF(LEN(G5)-LEN(SUBSTITUTE(G5,":",""))=2,LEFT(G5,FIND(":",G5)-1),"")

This formula returns the section BEFORE the last colon
(EDITED to correct a formula error)

I5: =MID(SUBSTITUTE(G5,":"&J5,""),LEN(H5)+1+(LEN(H5)>0),255)

In the above example, these values are returned:
AVM
Pioneer Clinic
Dave McAfee

Does that help?

**stemcell1** · 10-23-2008, 12:39 PM

That is probably the longest formula I have ever seen, but it worked!!!

Thank you Martind!!!!!

Trim formula

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Trim formula

Parsing text

Parsing text with a delimiter

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions