FIND MID & LEFT Functions

18 posts / 0 new
Last post
FIND MID & LEFT Functions


I need to extract the first character after the second space in a string to create a reference number. 
I seem to have partially figured it out but my formula picks the second space and the character I want to pick
please help.


customer name 


="ZMM/"&LEFT(F5,1)&MID(F5,FIND(" ",F5)+1,1)&MID(F5,FIND(" ",F5,FIND(" ",F5)+1),2)&"/"&MONTH(C5)&YEAR(C5)&"/01"

ZMM/AE H/92017/01

instead of 

Thanks in advance

Here's the correct formula


Your formula is close, but not quite there. Here's your formula:

="ZMM/"&LEFT(F5,1)&MID(F5,FIND(" ",F5)+1,1)&MID(F5,FIND(" ",F5,FIND(" ",F5)+1),2)&"/"&MONTH(C5)&YEAR(C5)&"/01"

Here's the correct formula. I've highlighted the corrections:

="ZMM/"&LEFT(F5,1)&MID(F5,FIND(" ",F5)+1,1)&MID(F5,FIND(" ",F5,FIND(" ",F5)+1)+1,1)&"/"&MONTH(C5)&YEAR(C5)&"/01"

Your original formula was taking two characters including the third space. This version takes one character starting with the first character after the third space.

Thank You very much

It works. thanks !!

AtoB airport taxi service:

AtoB airport taxi service: airport transfers & chauffeur service

Book your airport taxi now! Enjoy airport taxi transfer from your front door to the airport gate. No hidden fees. Only fixed prices and comfortable transfers.

Hello! I could have sworn I

Hello! I could have sworn I've been to this web site before
but after browsing through a few of the posts I realized it's new to me.
Nonetheless, I'm certainly happy I discovered it and I'll be book-marking it and checking back frequently!

Sweet blog! I found it while

Sweet blog! I found it while surfing around on Yahoo News.
Do you have any suggestions on how to get listed in Yahoo News?

I've been trying for a while but I never seem to get there!
Many thanks

Have you ever considered

Have you ever considered writing an ebook or guest authoring on other websites?
I have a blog based upon on the same ideas you discuss and would love to
have you share some stories/information. I know
my audience would enjoy your work. If you are even remotely interested, feel free to send me an e-mail.

Appreciate this post. Let me

Appreciate this post. Let me try it out.

I was suggested this web site

I was suggested this web site by my cousin. I am not sure whether this post is written by him as no one
else know such detailed about my problem. You are incredible!


If some one needs to be

If some one needs to be updated with most recent technologies afterward he must be
pay a visit this web site and be up to date every day.

I am really impressed with

I am really impressed with your writing skills and also with the layout on your blog.
Is this a paid theme or did you modify it yourself?
Anyway keep up the nice quality writing, it's rare to see a nice blog like this one
these days.

I'm impressed, I have to

I'm impressed, I have to admit. Seldom do I come across a blog
that's equally educative and amusing, and let me tell you, you have hit the nail on the
head. The problem is an issue that too few men and women are speaking
intelligently about. I'm very happy that I found this in my hunt for something relating to

Link exchange is nothing else

Link exchange is nothing else however it is simply placing the other person's weblog link on your page
at suitable place and other person will also do same in favor of you.

I will right away seize your

I will right away seize your rss as I can't to find your email subscription hyperlink or newsletter service.
Do you've any? Please allow me recognise in order that I may
subscribe. Thanks.

Hi there! I just wanted to

Hi there! I just wanted to ask if you ever have any problems
with hackers? My last blog (wordpress) was hacked and
I ended up losing several weeks of hard work due to no backup.
Do you have any methods to stop hackers?

The other day, while I was at

The other day, while I was at work, my sister stole
my iphone and tested to see if it can survive a 25 foot drop, just so she can be
a youtube sensation. My apple ipad is now broken and she has 83 views.
I know this is completely off topic but I had to share it with

I've been browsing on-line

I've been browsing on-line more than three hours these days, but I by no means discovered any fascinating
article like yours. It's lovely price sufficient for me.
Personally, if all webmasters and bloggers made just
right content material as you probably did, the web
will likely be much more useful than ever before.

naturally like your website

naturally like your website but you have to test the spelling on several of your posts.

A number of them are rife with spelling issues and I find it very troublesome
to inform the truth on the other hand I will surely come again again.

Add new comment