#1
|
|||
|
|||
How to create an account number based on partials from 2 fields
Hi, I'm struggling here - Please can someone help me?? I am wanting to create a unique account name/number using both numbers and letters from two separate cells. Cell C4 is the company Name. Cell C36 is the initial contact date. I want cell C2 to contain the "Account number" which is made up of the first 3 letters of the company name (in cell C4) and the dd/mm in cell C36 Eg - Company Name - Microsoft Initial contact date - 26/09/2014 I want cell C2 to contain MIC2609 HOWEVER - if the company name begins with "The" I want to eliminate this so it will skip to the next word in the company name - eg, Company Name - The Potting Shed Initial Contact date - 26/09/2014 I want cell C2 to contain - POT2609 Can anyone please help me with a function or formula for this please? Thank you |
#2
|
||||
|
||||
Please post a sample sheet - Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
This formula works kind of:
=UPPER(IF(LEFT(D4,4)="THE ",MID(D4,5,3),LEFT(D4,3)))&DAY(D36)&MONTH(D36) except it only displays one digit if the day or month is less than 10. This can be worked around with a few ifs: =UPPER(IF(LEFT(D4,4)="THE ",MID(D4,5,3),LEFT(D4,3)))&IF(DAY(D36)<10,"0","")& DAY(D36)&IF(MONTH(D36)<10,"0","")&MONTH(D36) |
#4
|
|||
|
|||
Thanks gebobs,
I had to change it to reference cell C3 and C36 but it works perfectly! Thank you so much, you're a life saver!!!! |
Tags |
formula |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to create a table with a number of line depending a number entered by user | Cellendhyll | Word Tables | 3 | 07-10-2014 05:49 AM |
Maximum Number Of Styles-Based-On? | Pluviophile | Word | 13 | 05-08-2014 05:29 AM |
(ask) unable to create account | jackyjacky88 | Outlook | 1 | 04-06-2014 09:10 PM |
Error: 'Cannot redeem this offer based on how your Microsoft account is setup' | Laylo | Office | 0 | 08-10-2013 12:49 AM |
Outlook 2007 cant create new account | colinwill | Outlook | 0 | 01-30-2013 12:04 AM |