# Trimming cells (not spaces)

1. ## Trimming cells (not spaces)

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

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

3. 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

4. Another,
``Please Login or Register  to view this content.``
Hope this helps.

5. =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.

6. 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?

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

8. If the 4 characters between / are to be used then

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

9. 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.

10. Mine looks more impressive!!!! haha

11. 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.

12. 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?

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

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

15. 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!!

16. the above returned "Exclude" by the way, which is correct!

There are currently 1 users browsing this thread. (0 members and 1 guests)