Custom Cell format

[iLuvbase]

I need to create a custom cell format that is immune to how someone enters types their data.

Basically we generate quotes on a Q# basis, but we may have multiple configurations for a single quote number.

e.q - we may have Q4457, Q4457A, Q4457B, C - etc.

However - coworkers enter data in different manners.

For example - if I enter 4457, I want the cell to display Q4457.

If I enter Q4457 I want it to read Q4457.

If i enter 4457A, I need it to read Q4457A.

If I enter Q4457A, I need it to read Q4457A.

Things I've tried -

Q# - Displays properly for 4457 or Q4457, and Q4457A - does not work for 4457A (Shows only 4457A, no leading Q)

Q@ - Displays properly for 4457 and 4457A - Does not work for Q4457 or Q4457A (displays 2 Qs at beginning)

 

[grizz]

well if you can get along without merging the cells --- just stretch them out and use the code that way until you can upgrade or figure this out

 

[iLuvbase]

question -- how are your cells merged

Missed this question - sorry.

Left Click multi-cell highlight beginning AM2 ending on AR2, then doing CTRL+1 to format, click on Merge Cells checkbox.

 

[grizz]

Merged Cells in Excel 97 are a pain for the very reason you found - you can't do much with them but look at them!

You need to change the cells from "Merged" to "Centre across Selection", which looks like Merged but can be processed.

First select the merged Column or range of cells, assuming that all rows of the column are identically merged, and then go to Format / Cells / Alignment. You should find that there is a tick against Merge Cells - turn it off. Then below Horizontal, under Text Alignment, select the option "Center across Selection", and then OK and your problems should be solved.

 

[Rick Rothstein]

I don't have access to Excel 97, so I have no way to try and jury-rig a work-around for you. Sorry. All I can say is the code I posted works fine on my copy of XL2003. I guess I'll have to leave you in the hands of those who know something about Excel 97 and hope they will be able to help you out.