Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ron
 
Posts: n/a
Default Use Validation to force specific entry of first 3 characters in st

What formula should I enter into Category "Custom" of Validatioan to force
user to begin character string with "PR-"? Desired result is "PR-001". I
don't want user to have option to enter "001" or "002", etc., without the
"PR-". Nor do I want them to be able to enter "DR-001".
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default Use Validation to force specific entry of first 3 characters in st

Hi

An example for column A, the formatting starts from A1
=(LEFT($A1,3)="PR-")

Whe you also need all entries to be unique, then
=AND(LEFT($A1,3)="PR-",COUNTIF($A:$A,$A1)<2)


Arvi Laaneemts


"ron" wrote in message
...
What formula should I enter into Category "Custom" of Validatioan to force
user to begin character string with "PR-"? Desired result is "PR-001". I
don't want user to have option to enter "001" or "002", etc., without the
"PR-". Nor do I want them to be able to enter "DR-001".



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Use Validation to force specific entry of first 3 characters in st

=LEFT(A1,3)="Pr-"
or maybe...
=EXACT("PR-",LEFT(A1,3))

(with A1 being validated.)

ron wrote:

What formula should I enter into Category "Custom" of Validatioan to force
user to begin character string with "PR-"? Desired result is "PR-001". I
don't want user to have option to enter "001" or "002", etc., without the
"PR-". Nor do I want them to be able to enter "DR-001".


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ron
 
Posts: n/a
Default Use Validation to force specific entry of first 3 characters i

You people are really really good. Thanks. I modified the formula to ensure a
6 digit entry:
=AND(LEFT(E26,3)="Pr-",LEN(E26)=6)

"Dave Peterson" wrote:

=LEFT(A1,3)="Pr-"
or maybe...
=EXACT("PR-",LEFT(A1,3))

(with A1 being validated.)

ron wrote:

What formula should I enter into Category "Custom" of Validatioan to force
user to begin character string with "PR-"? Desired result is "PR-001". I
don't want user to have option to enter "001" or "002", etc., without the
"PR-". Nor do I want them to be able to enter "DR-001".


--

Dave Peterson

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ron
 
Posts: n/a
Default Use Validation to force specific entry of first 3 characters i

You people are really really good. Thanks. I modified the formula to ensure a
6 digit entry:
=AND(LEFT(E26,3)="Pr-",LEN(E26)=6)

"Arvi Laanemets" wrote:

Hi

An example for column A, the formatting starts from A1
=(LEFT($A1,3)="PR-")

Whe you also need all entries to be unique, then
=AND(LEFT($A1,3)="PR-",COUNTIF($A:$A,$A1)<2)


Arvi Laaneemts


"ron" wrote in message
...
What formula should I enter into Category "Custom" of Validatioan to force
user to begin character string with "PR-"? Desired result is "PR-001". I
don't want user to have option to enter "001" or "002", etc., without the
"PR-". Nor do I want them to be able to enter "DR-001".




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to format only specific characters or numbers within each cellwithin a range of cells Colleen Excel Discussion (Misc queries) 4 September 12th 05 10:04 PM
trim a string by specific number of characters windyoldman Excel Discussion (Misc queries) 2 July 13th 05 01:53 PM
how do I highlite text within a cell (specific characters) tim Excel Discussion (Misc queries) 1 May 20th 05 05:23 AM
Replacing specific characters with spaces Night Owl Excel Worksheet Functions 3 May 13th 05 05:52 PM
Pulling out specific characters Louis Excel Discussion (Misc queries) 4 April 22nd 05 10:05 PM


All times are GMT +1. The time now is 07:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"