Please help! This is probably easy for you guys, not me!
I have a spreadsheet with National Insurance Numbers in one column, and I need the format code for 'custom' when you go into format cells to remove the last digit automatically. for example, if the NI Number was AB123456C, I need the formula to automatically remove the 'C'
thank you! :thanx:
Re: Format cells...
Jamie,
Using formatting you will not be able to change the content of the cell or hide characters because you have text. With numbers you could simply use rounding.
However using a formula you could drop the last letter:
Left(A1,8) will give you the left 8 characters in the string.
HTH,
Alan.
Re: Format cells...
cheers Alan, solves my problem.
Another question I dont know if this is even possible. My Spreadsheet is full of formulas, and I need them removed (programme I use at work dosnt accept the sheet if there is formulas in the background) but I still need the text in the cells to remain the same as the formulas would give. Is this possible?
Re: Format cells...
Jamie,
Yep.
The basic process is copy and paste special values. You need to do this for each worksheet.
Save the workbook as "currentname (values)" or something similar.
Goto the first sheet in the workbook and press cntrl+A to select all cells
Copy the cells in any way you wish (click the icon, select edit > copy or ....)
Select Edit > Paste Special and a dialog box appears, you can now select values and OK.
The worksheet will have all values still but no formulas.
Repeat for each worksheet.
However, just had a thought. If the problem is that you can not import a worksheet with formulae then you could simply do a search and replace on the =. To do this:
Save the workbook as "current name (formulae)" or similar.
Select the first worksheet.
Cntrl+a
Cntrl+h
Enter = in the first box and ### in the second box.
Replace all.
Now you have a sheet that has the formulae but as text.
When you open it at work you can do another search and replace swapping ### for the =.
HTH,
Alan.
Re: Format cells...
magic, cheers mate
Don’t have an account yet? Register yourself now and be a part of our community!
