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!
Last edited by VBA Noob; 10-23-2008 at 12:46 PM.
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
The part up to the last colon
Please Login or Register to view this content.
Note: CHAR(7) is the ASCII code for sounding a PC BellPlease Login or Register to view this content.
Is that something you can work with?
Last edited by Ron Coderre; 10-23-2008 at 11:11 AM. Reason: add a comment
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!!
well
for first part
for second partPlease Login or Register to view this content.
where a1 contains your original textPlease Login or Register to view this content.
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
This formula returns the AVM part....only if there are 2 colonsPlease Login or Register to view this content.
otherwise it is blank ("")
This formula returns the section BEFORE the last colonPlease Login or Register to view this content.
(EDITED to correct a formula error)
In the above example, these values are returned:Please Login or Register to view this content.
AVM
Pioneer Clinic
Dave McAfee
Does that help?
Last edited by Ron Coderre; 10-23-2008 at 12:47 PM. Reason: edit the formula for cell I5
That is probably the longest formula I have ever seen, but it worked!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks