Conditional Proper Case

**billgyrotech** · 03-02-2016, 12:01 PM

I have this sample data in Excel and would like to know if it could be edited to proper case with conditions. The part numbers need to remain upper case but the words should be proper case. The first column is before and the second is the result I would like. I have about 20K records so it would be nice to automate the process.

Does anyone know of a way to accomplish this? Thank you

[Code]
99999 TRUNNION ASSEMBLY 99999 Trunnion Assembly
99999 SENDING UNIT 99999 Sending Unit
CE2150H13YY25 CE2150H13YY25
CE2151H15YY15 CE2151H15YY15

[Code]

I tried to paste the table but the data shifted sorry.

**DBY** · 03-02-2016, 01:11 PM

Hello
If your data syntax is consistent, as in your four examples, and your Code of numbers is always followed by a space and the cells with the Codes alone have no space then a macro or formula could convert them. For example this macro:

Please Login or Register  to view this content.

The code would go through A1:A20 and place the converted string in the adjacent cell. If all is well you could then paste them in to your actual data field. You could of course overwrite the data directly but probably isn't a good idea in case it's not correct as VBA nullifies the Undo option.

Another alternative would be a helper column with formulas to do the same thing.

Formula:

Please Login or Register  to view this content.

Everything does depend on the consistency of your original data layout.

Hope this helps.
DBY

**billgyrotech** · 03-02-2016, 01:35 PM

Sorry I should have pasted more examples of the data.

52D267M
1C970-982-9999
1CC258
9B56B5698BNM
VENDOR PART 2568KKP
OSCILLOSCOPE TEK 475BVX

I guess where ever there is a word it should be proper case. Does that help? I really appreciate any assistance. I will make a backup copy before doing any changes in case it doesn't turn out correctly.

**Kevin#** · 03-02-2016, 01:42 PM

You have provided very little sample data, but

If non Part Numbers always commence with 99999 then try this formula in cell B2 and copy down (assumes records are in column A starting at A2)

=IF(LEFT(A2,5)="99999",PROPER(A2),UPPER(A2))

I also notice that everything is duplicated with a space in the centre. If this is consistent then this may help. If the above works, then put this formula in cell C2 and copy down.
It removes trailing and leading spaces etc and that should leave an odd number of character because of central blank space. It leaves the left half of the cell adjusting for that extra character.

=LEFT(TRIM(B2),(LEN(TRIM(B2))-1)/2)

And if you then want to eliminate the 99999, put this formula in cell D2 and copy down
(removes first 6 characters of the cell)

=IF(LEFT(C2,5) = "99999",RIGHT(C2,LEN(C2)-6),C2)

EDIT
This post looks irrelevant looking at post#3

**billgyrotech** · 03-02-2016, 01:48 PM

Sorry for the confusion. I wanted to post the file itself but there are over 20K records. I will attempt to make the edits with what was provided thus far thank you.

**DBY** · 03-02-2016, 02:08 PM

Yes it's tricky with the layout. There's not really any way I know of telling Excel what constitutes a legitimate word and what is a code number as the codes are often text strings.

You could perhaps break the strings apart at the spaces convert them to proper case and then concatenate them again. If I think of a way I'll add another post.

**Kevin#** · 03-02-2016, 02:18 PM

How about posting a cut-down version of the file - say the first 500 lines

**billgyrotech** · 03-02-2016, 02:19 PM

Okay I will do that give me a few minutes please.

**billgyrotech** · 03-02-2016, 02:27 PM

I am trying to add the file but it won't let me do it. Any ideas why? It is a macro enabled file does that matter?

**shg** · 03-02-2016, 02:28 PM

Cross=posted at http://www.mrexcel.com/forum/excel-q...oper-case.html

**billgyrotech** · 03-02-2016, 02:31 PM

Hey thanks for the fantastic support.

Conditional Proper Case

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Proper Case

Re: Conditional Proper Case

Re: Conditional Proper Case

Re: Conditional Proper Case

Re: Conditional Proper Case

Re: Conditional Proper Case

Re: Conditional Proper Case

Re: Conditional Proper Case

Re: Conditional Proper Case

Re: Conditional Proper Case

Re: Conditional Proper Case

Thread Information

Users Browsing this Thread

Similar Threads

Upper Case or Proper Case help

[SOLVED] Need the proper syntax to use Case/Select Case in vba properly.

[SOLVED] Proper Case using VBA

Excel 2007 : Change Address Database from upper case to proper case

proper case

excel'03 how to convert a column from upper case to proper case

[SOLVED] Excel: How do I change all upper case ss to proper case?

[SOLVED] Changing Upper case to Proper Case

Bookmarks

Bookmarks

Posting Permissions