Is Nth Character Text Or Number
Aug 16, 2007
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?
View 7 Replies
ADVERTISEMENT
Apr 1, 2008
I've tried and tried and I con not get it right. In A1 I have a validation, it onlu allows to enter a 6-digit number between 100000-899999 or the same numbers with the letter I in front, that is I100000-I899999. Now I need a macro to check that cell F2 is not empty if the value in Cell A1 is above 299999. And this should also apply if there is an I in front. The function for the validation is:
=IF(AND(LEFT(A17)="I";--MID(A17;2;6)>100000;--MID(A17;2;6)<900000);OCH(A17>100000;A17<900000))
The code I have is:
If Range("A" & i).Value > "299999" Then
If Range("F" & i).Value = "" Then
MsgBox "Indicator missing on row " & i
Application.Goto .Range("F" & i)
Exit Sub
End If
End If
It works wor the nubers without the I but it demands a vlaue in F on all I numbers and I only want it for >I299999.
View 5 Replies
View Related
Aug 14, 2014
I am looking for a way to show the character location number of a text file, possibly in the first row or a macro that I can run at any given location that will give me the location # I am currently viewing.
Currently, when I open the file I can see the character # at the opening screen (see attached file) but they disappear when it actually converts. I would like to be able to keep the character location ruler once the file is opened in Excel so I don't have to manually count.
View 1 Replies
View Related
Jul 9, 2014
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??
View 6 Replies
View Related
Oct 23, 2008
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?
View 2 Replies
View Related
Feb 6, 2009
I 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.
View 11 Replies
View Related
Aug 15, 2006
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 Related
Jan 9, 2007
In 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
Jan 29, 2013
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 "*". (**********)
View 2 Replies
View Related
Feb 4, 2010
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
View 3 Replies
View Related
Sep 3, 2009
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 Related
Oct 5, 2009
They 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
View 4 Replies
View Related
May 9, 2013
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.
View 2 Replies
View Related
Jan 9, 2014
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
View 5 Replies
View Related
Dec 30, 2008
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
View 9 Replies
View Related
Feb 12, 2008
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.
View 3 Replies
View Related
Aug 11, 2009
Pretty sure this has been asked but have searched the forum to no avail, but I need to extract the numbers from a value which has a letter on the end.
eg. 1000x I need to get out the 1000
or 2p I need the 2
I have sooooo many values ranging in numbers of digits, so just basic left(A1,2) won't work, I'm sure I've seen a search or find function but don't know how to use them!
View 8 Replies
View Related
Jan 31, 2009
Need formula to count the number of times a particular character is found in a string I have been experimenting with the likes of
View 5 Replies
View Related
Mar 23, 2009
I need to make a list of Part Numbers in quotation marks. If a number of digits of a P/N is less than 13 a number of space characters has to be added to make the string 13 characters long.
We have example P/N:
1234567890123
12345678
123456
should become:
"1234567890123(no extra space characters should be filled-13chars)"
"12345678(5 extra space characters here)"
"123456(7 extra space characters)"
Is there a function that inputs a cerain number of a specific character ("space" in this example)?
View 2 Replies
View Related
Dec 3, 2012
I have a requirement where I have to count number of "-" character in particular row
I am using a excel database for promotion records of my team....The data base works as follows. I am using countif function to calculate number of associates in specific grade for specific month. One associate is getting promoted to next level, I will add one more row with the same employee name with change in designation and diff start and end date. I want to generate report for monthly promotions, The report should depict number of promotions in each month. ie in Jul there is one promotion from trainee to Engg for EMP1. There are hundreds of employees, eligible for promotion in each month.
A B C D E F G H I J K L
Emp Name Desig From Date To Date Apr-13 May-13 Jun-13 Jul-13 Aug-13 Spt 13 Oct-13 Nov-13
EMP 1 trainee 4-1-13 6-30-13 trainee trainee trainee - - - - -
EMP 1 Engg 7-1-13 11-30-13 - - - Engg Engg Engg Engg Engg
View 2 Replies
View Related
May 23, 2008
How can I extract mid section of the string which is always 5 characters long and is always followed by a period "." ?
My account strings are in 3 sections (but the third section isn't always used)
For example,
1.10210
1.22556.001
900101.56201
955261.54444.001
5566625.58886.957756
View 4 Replies
View Related
Feb 23, 2013
get the function to get the text in the middle of long text
i have text like this 125/565/797/7222/222/2122
how i put the formula to get text after multiple "/" example the text : 125/565/797/7222/222/2122 in cell A1 i want to get after third char "/ " value 7222 Before the fourth "/"in cell A2 and after the last "/" in cell a3 or value 2122
View 4 Replies
View Related
Aug 15, 2007
I have a cell in my worksheet with a fully qualified filename like 'D:abcDefGHIJklxyz123.app
I would like to extract only the xyz123.app. Obviously, the number of characters is going to vary based on the filename. The find and search functions appear to locate the cell but not the substring in the text. The right and left appear to work based on number of characters and in my case these are varying. Also, there does not appear to be any function that can do a search a string from right to left.
View 6 Replies
View Related
Apr 18, 2009
The following code reads text one character at a time, and each character is determined either to be a blank space or to be any other character. If it's determined to be any other character, then the character is added to the active cell. If it's determined to be a blank space, then the active cell becomes the cell one column over. The idea is to have different words written in columns next to each other.
The sample text file I'm reading from reads: "text file". The file has only those two words. One space between them. No space before the first word, and no space after the second word. The following code compiles and runs. BUT the result is that the word "text" is in column k, not A. And the word "file" is in column J, not b.
View 2 Replies
View Related
Jul 4, 2012
I have a list of over 300+ email addresses. If there is a way that I could pull out the ".com" (Find & Replace: Which is not working in any format, Text, Custom, General.) then pull out the text from the right to left until I get to the "@" into another cell and drag that down that would make it so much easier. Is there a formula that will pull data and then stop at a CHAR code? @ =CHAR(64) These email addresses are all different lengths. (personal to corporate email addresses.)
Example: moe10134@hotmail.com
Looking for something like this: Replace ".com", =LEFT(9) or whatever will take out the "moe10134@" and the ".com" leaving only hotmail.
View 3 Replies
View Related
Mar 29, 2013
I am trying to remove the last character from some text with:
Application.SendKeys "{F2}"
Application.SendKeys "{BS}"
Application.SendKeys "{Enter}"
But when I do this, it is giving me the object browser in VBE . . .
View 4 Replies
View Related
May 12, 2014
I have list like the below. I need to pull out everything before the space for each of these and after the _ which is after the CRN_, SA_ or MA_
CRN_212141 JRDC 7402-01_ICT-ICM IT Services Basel PDiv 3702 POPEnd 01/31/2015
MA_7500007822 JRDC
SA_GS-35F-4461G No Alias Determined
CRN_179764 Director of Administration and Managem PDiv 3799 POPEnd 06/30/2014
MA_N00189-09-D-Z044 USJFCOM Projects
SA_GS-35F-4461G No Alias Determined
Desired Results:
212141
7500007822
GS-35F-4461G
179764
N00189-09-D-Z044
GS-35F-4461G
View 2 Replies
View Related
Dec 20, 2006
I have a column list that I need to seperate. The Text has both a title and location separated by an = sign. The cells look like this...
BlahBlahBlah = Location1
I would like to take the text after the = sign and place it in the next column so that all that is in it is...
Location1
The Location varies so the RIGHT function won't work.
View 3 Replies
View Related
Aug 15, 2007
You know how you can combine different cells and concatenate their values? Can you reverse that? For Example, I have this value "Yambao, Mikhail P." and I want to break them apart so I can use "Yambao" as a reference value to fill up other information concerning that name.
View 9 Replies
View Related
Aug 15, 2007
I have a cell in my worksheet with a fully qualified filename like 'D:abcDefGHIJklxyz123.app
I would like to extract only the xyz123.app. Obviously, the number of characters is going to vary based on the filename. The find and search functions appear to help locate the cell but not the substring in the text. The right and left appear to work based on number of characters and in my case these are varying. Also, there does not appear to be any function that can do a search a string from right to left.
View 5 Replies
View Related
