Trimming cells (not spaces)

**TheRetroChief** · 10-20-2008, 09:38 AM

Afternoon all,
Let's say in a cell i have the text ABCD/0123/01234 or ABC/0123/012345
How would i go about extracting whatever is inbetween the / ? i.e. 0123 in both examples??
Ta muchly

**Andy Pope** · 10-20-2008, 10:28 AM

=mid(a1,find("/",a1)+1,find("~",substitute(a1,"/","~",2))-find("/",a1)-1)

**TheRetroChief** · 10-20-2008, 10:43 AM

Top drawer, works a treat.
As i like to understand and learn from these forums rather than just get an answer, when you have a moment, would you mind quickly running through what each part of the formula does exactly so i can understand and adapt for future?
Muchos Gracias

**snasui** · 10-20-2008, 10:46 AM

Another,

Please Login or Register  to view this content.

Hope this helps.

**Andy Pope** · 10-20-2008, 10:48 AM

=mid(a1,find("/",a1)+1,find("~",substitute(a1,"/","~",2))-find("/",a1)-1)

MID function returns text from specified start point for specific length.

So specified start is determined by using

find("/",a1)+1

In order to determine length we need to find second occurance of / and substract first occurance. So in order to find the 2nd occurances I used substitute to change the character to one not normally found in cells.

substitute(a1,"/","~",2)

Then we can use a Find to locate the ~ character.

**TheRetroChief** · 10-20-2008, 11:03 AM

Ah. Clever!

So if there were three i'd have to substitute the third for something else?

If i wanted to create a date in the form of mm/yy from what was generated, how could i incorporate putting a date seperator (/?) in the middle?? e.g. ABCD/0908/01234 becomes 09/08 or September 2008 or whatever?

**TheRetroChief** · 10-20-2008, 11:07 AM

Never mind.....i've done it with =left(cell,2)&"/"&right(cell,2) !!!

**Andy Pope** · 10-20-2008, 11:08 AM

If the 4 characters between / are to be used then

=MID(A1,FIND("/",A1)+1,2) &"/"&MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",2))-2,2)

**TheRetroChief** · 10-20-2008, 11:13 AM

That's the beauty:
=LEFT(MID(AI2,FIND("/",AI2)+1,FIND("~",SUBSTITUTE(AI2,"/","~",2))-FIND("/",AI2)-1),2)&"/"&(RIGHT(MID(AI2,FIND("/",AI2)+1,FIND("~",SUBSTITUTE(AI2,"/","~",2))-FIND("/",AI2)-1),2))

Where AI2=cell with the text in.

Result.

**TheRetroChief** · 10-20-2008, 11:14 AM

Mine looks more impressive!!!! haha

**TheRetroChief** · 10-20-2008, 11:42 AM

PROBLEM!!!!

The resultant text in the cell (theoretically mm/yy) for some reason cannot be interpreted as a date.

Basically what i want to do is check if the date generated is before July 07,
i have 07/07 (July 2007) in cell AP1 with your formula in AP2 it looks at the cells in AI to get the the four-figure number and change it to a date.
Formatting has no effect.

**ChemistB** · 10-20-2008, 11:58 AM

The result will be taken by Excel to be a string (text). Add +0 to your formula and Excel will see it as a number/date. Does that work?

**TheRetroChief** · 10-20-2008, 12:17 PM

Sneaky...yes that fixes it. Good shout!!!

**TheRetroChief** · 10-20-2008, 12:23 PM

Tell a lie....it interprets all the dates as 2008. So no, not quite!!!

**TheRetroChief** · 10-20-2008, 12:45 PM

Thanks guys, I've bodged it as follows....

If in AI2 i have

ABCD/0607/01234

In AP2 i have
=(MID(AI2,FIND("/",AI2)+1,2)&"/"&(MID(AI2,FIND("/",AI2)+1,2)&"/"&MID(AI2,FIND("~",SUBSTITUTE(AI2,"/","~",2))-2,2)))+0

This returns the date 06/06/07

In AQ2 i have
=IF(AP2<$AP$1,"Exclude","Include")

in AP1 i have 01/07/07

It's not ideal, but seems to work!!