I'm trying to determine if the last character of a cell is a number or not a number. So in a cell I may have a string like:
ZXC123
or
ZXC123A
If the last character is not a number I want the letter returned. So I thought I could use a formula like: =IF(NOT(ISNUMBER(RIGHT(D88,1))), RIGHT(D88,1),""). However for the string ZXC123 it still returns '3' so anyone have any ideas how I can do this?
I received a request from a coworker regarding custom formatting some numbers in his spreadsheet. Those numbers are serial numbers of 20 characters long. Sometimes in my files I use this custom number formatting ###0 and its enough for the data I handle. But when I tried to use it in his spreadsheet, the following shows:
8456891070060510000
The cell must look like this: 08456891070060510302
The reason to have it like this is due to a Delivery Program requirement to deliver Set-top Units for repair. The Delivery Program do not recognize other format than the above. My coworker takes the data from a spreadsheet, and the spreadsheet needs a custom number format to display the correct number.
find a custom number format to be able to display as my coworker need it??
I have a column of random number in Column A and need a formula to identify if any of the numbers in Col A is a prime number. If it is, then say "Prime" in the cell adjacent to the prime number in Col B. Eg if A12 has a value 17, then cell B12 should say "Prime".
Determine If A Number Is Even Or Odd. i would like to Determine If A Number Is Even Or Odd...?
View 3 Replies View RelatedIs it possible to programmatically determine the page number on which a specific cell will print?
View 14 Replies View RelatedTrying to do a simple workbook here, with hopefully no macros/VB script.
Essentially, I want a cell to display the row number (-1 to take into account the heading) which has the last value contained in it.
The workbook is a list of users that have signed up in 1 month, and is distributed to staff for a particular reason.
The cell I'm trying to create is part of a 'stats' section.
I have a worksheet that contains active rows and hidden, inactive rows. On occasion, I would like to unhide an inactive row. I intend to do this by running a macro which will generate an input box to enter the file number of the row in question. I can restore the row, but what I am unable to find a solution for is converting the file number to the row number to be restored. The file numbers reside in column A, so if I input file number 2300, I need the macro to look through column A, find the cell that contains the value 2300, and return (as a string?) the row number that contains that value. From this I can have that row returned to visible status.
View 9 Replies View RelatedWe have list for entries that is shared among few people. In that list, we manually add number for the entry. So if I input 99 then, next person should add 100 and so on. I would like to automize this, so say If I use this, then no one else can use it in the mean time and then when number is input, it should disallow if same number is input and it should automatically enter the next number.
View 3 Replies View RelatedI have a number of entries in column A.
The format is:
A1 B680192 Middle School
A2 Office Building Project
and so on;
I want to test if char.s 2 through 7 are numbers.
I can seperate one char at a time and use CODE function to check if the result is between 48 and 57.
I know how to split all six char.s at a time e.g. 680192, but I don't know how to test to see if it is a number?
ISNUMBER function doesn't work.
If I can do it with excel function, it would be better. If VBA has to be used, so be it.
I have a spreadsheet which I automatically generate using VBA. One of the columns lists account numbers. After the macro is complete, the account numbers are non-numeric and left justified. However if I select one of the account numbers by clicking on its cell, and then edit the account number in the formula window, it changes the cells property to numeric and right-justifies the cell.
View 6 Replies View RelatedIn column N I would like a formula to add 3 columns,e.g. C and D and F. The problem is I want the symbol ~ replaced by 1.0 for calculation purposes.I need a solution in 1 column,if possible. I have made a few efforts,but just not getting there. See attached section of sheet.
View 2 Replies View Related Cell B23:687968307=ISTEXT(MID(RIGHT(B23,3),1,1))TRUE
Cell B24:C8796B07=ISTEXT(MID(RIGHT(B24,3),1,1))TRUE
I'm trying to test whether the third last character (3 and B) is a text or not.
As you can see, the reslts are both TRUE, but I'm expecting to see that the first one should be FALSE, as '3' is a number right?
Likewise, if I replace the formula with ISNUMBER instead of ISTEXT, the results are both FALSE, but shouldn't the first one be TRUE, again, because the '3' is a number?
Have I missed something in the formula, or is there a better way of expressing this formula?
I have a report of time balances that employees enter. I need to run a macro that looks at each cell and determines if the number is divisible by 4. (We only track vacation and ill time in 4 hour increments) If the number the employee entered is not evenly divisible by 4, I want the cell highlighted.
All of this I have figured out, the loop to look at each row, the IsNumeric function to test if there is a number...
My problem is with the MOD. I figure I should be testing number MOD number, but it is not reliable.
4.1 Mod 4 returns 0
.5 Mod 4 returns 0
I just need it to return zero if the number is evenly divisible by 4.
I considered the RoundUp function, but then it would also round anything above 3 hrs to 4, and that won't work.
I have client sheets that need to be printed at the end of each month. Sometimes I need 1 sheet, sometimes more. I have a macro which pops up a userform in which I can select the client I want to print and the month. I would like to be able to have the macro determine how many pages need to be printed so I get all the pages and do not end up with any blank pages. Is this possible?
View 1 Replies View RelatedIt's been awhile since I've done this...and I tried searching for the solution but to no avail.
I'm trying to edit the contents of a series of numbers by using the Rounding function. When I execute my macro on the next cell, it uses the value of the original cell when the macro was created. Here's a copy of my macro:
Sub RoundTest()
'
' RoundTest Macro
' Macro recorded 7/26/2007 by Aramark Uniform Services
'
' Keyboard Shortcut: Ctrl+l
'
ActiveCell.FormulaR1C1 = "=ROUND(2103.86,0)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
1. In column A i have a list from 1-12 (months in a year)
2. In column B i have the formula that references the corresponding cell in column A in the same row i.e. B1 "=(A1={1;3;5;7;8;10;12})"
Problem: This formula works only when the value in column A equals "1"...i need it to work when it equals any of the identified numbers in the set.
I have a number and i wanted to convert it to a number or character.
Ex.
if i have value of 7, should be converted to a character of "*", the output will be 7 times of "*" (*******)
if value is 10, so it will be 10 times "*". (**********)
I need composing a formula that will add a space after every 4th Number in a 16 digit Character Set.
ex. 0101 0101 0101 0101
I have a cell range that is passed as a String to a function, and within that function I need to extract only the Column letter. If it was just 1 letter it would be simple, but it may be 2, so does anybody know of a way of testing to see if the second character is a letter or a number?
View 5 Replies View RelatedThey have a list of data numbers which are yy/#####/@@@@@ (2digit year/casenumber/alphanumeric code). Where some people have typed in the code wrong (6 or more digits in case number) we want to know is there a way of doing this.
Ideally I want something that says "If 9th character is a slash then yes, if not no" or "if 9th character is a number then yes, if not, no".
Example of data is (where we want third option highlighted as erroneous (6 not 5 numbers in middle)
09/56487/KFJT
09/42105/PLOUR3
09/002145/PLIFD
09/02145/ASDF
Is there a way of getting the first three character of every word or number in a cell ( seperated by spaces) ?
for example in cell E2 I have
BASE 150 WALL COLOUR GREEN
and would like it to read
BAS150WALCOLGRE
I have a approx a 1000 rows that I would like to do this with.
I need to limit the number of Characters that can be inputted to the popup InputBox to just 31. How do I set MaxLength? Code I am using is listed below.
Code:
Sub NewRecipeSheet()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("1. Recipe Master Sheet")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
Application.Goto Reference:=Sheets("1. Recipe Master Sheet").Range("A1")
Range("A1").Value = InputBox("Menu Item Name?")
End Sub
I have part numbers in a column that look like the numbers listed below. All numbers begin with A, so the default sort begins with the first number. I would like to sort the list using the middle three numbers denoted by the red x's in the first example. Is this possible?
A 385 XXX 0055 A 385 466 0060 A 385 466 0160 A 385 584 7024 A 387 284 0185 A 388 017 0160 A 389 260 1485 A 389 262 0293 A 389 262 4935 A 389 262 9134 A 389 267 2819 A 389 267 3319 A 393 328 0065 A 398 267 3319 A 403 990 0210 A 403 997 0620 A 404 260 0074
I have an Excel file that quiries Google for driving distance (someone helped me with this.) It returns the correct miles but has some 'extra' characters. Example:
31.1&nb or 886&nbs
So the answer always has &nbs after the mileage. I tried the following function which works when there is a decimal but fails when there is not. =REPLACE(MID(E2,1,SEARCH("&",E2,1)-1),SEARCH(".",E2,1),1,","). Can this be done with a function or must it be VB script? Which is fine if it is.
I have two columns of data with a minimum value in the first column and a maximum value in the second column. What I'm trying to get is an indicator in a third column which tells me if a whole number falls at or between the minimum and maximum values. Here is a sample of my data:
A1 B1 C1
0 0.1 TRUE
0.2 0.5 FALSE
0.6 0.8 FALSE
0.9 1.2 TRUE
1.3 1.6 FALSE
1.7 2.0 TRUE
is it possible if I have a directory like the following
[Code].....
with .csv files which are placed in arithmetic order 01,02,03 etc to determine if one .csv file is missing and print an error that this file is missing? I think that if I knew how many files I got I would create an array and check one by one the files.. But in case I have a dynamic directory and the number of files is not the same all the time , is this possible?
if the active cell is formatted as number and I write
x = Cells(Row, Column).Value
and the cell is empty or blank, x will be set to 0.
How can I determine that the cell is blank and not actually 0 ?
Is there a formula I can use to determine the number of work days in each week for a given month. The work week would start Sunday, with Friday and Saturday as days off. Each week would be in their own row.
i.e.
January
Week 1: 2 days
Week 2: 5 days
Week 3: 5 days
Week 4: 5 days
Week 5: 3 days
[Code] ..........
How can I interrogate the contents of a cell to determine whether or not it contains a number formatted as text? I intend to run a .value = .value over each such cell to convert it to a number format, but I only want to do this to relevant cells.
View 9 Replies View RelatedHow can we determine Number of Logical Pixels per Inch based on the system configuration using API and return the value to Cell A1 of Sheet1.