Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-26-2014, 07:59 AM
Sonataarctica2301 Sonataarctica2301 is offline How to create an account number based on partials from 2 fields Windows 7 32bit How to create an account number based on partials from 2 fields Office 2007
Novice
How to create an account number based on partials from 2 fields
 
Join Date: Sep 2014
Posts: 2
Sonataarctica2301 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-26-2014, 09:47 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to create an account number based on partials from 2 fields Windows 7 64bit How to create an account number based on partials from 2 fields Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 09-29-2014, 09:52 AM
gebobs gebobs is offline How to create an account number based on partials from 2 fields Windows 7 64bit How to create an account number based on partials from 2 fields Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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)
Reply With Quote
  #4  
Old 10-01-2014, 01:42 AM
Sonataarctica2301 Sonataarctica2301 is offline How to create an account number based on partials from 2 fields Windows 7 32bit How to create an account number based on partials from 2 fields Office 2007
Novice
How to create an account number based on partials from 2 fields
 
Join Date: Sep 2014
Posts: 2
Sonataarctica2301 is on a distinguished road
Default

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!!!!
Reply With Quote
Reply

Tags
formula

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create an account number based on partials from 2 fields 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft