Free Microsoft Excel 2013 Quick Reference

if alpha character

The following cells:

00000000000A
00000000000B
00000000005

if right(a1)=alpha character then everything left of the character, else the
entire field.

How do I code this?


Post your answer or comment

comments powered by Disqus
Dear All,

I need to convert part of a string to a number. the string can be x
characters long, but I only want the first teo characters, where they are
numbers.

I am having trouble catching cases where wither first or second character is
alpha.

If (Mid(sshift, 2, 1) = "A") Or (Mid(sshift, 2, 1) = "P") Then
iShftStart = CInt(Left(sshift, 1)) ' case where shift is written as 8AM or
8PM
Else
iShftStart = CInt(Left(sshift, 2))
End If

my conditions need to include any ascii alpha character, really.

Any suggestions?

Many thanks,

Matilda

Hi everyone,

I have some VBA code that works well whenever ProjNoB is a number (eg 211147) or has other characters in it (eg 211147/1), but whenever it contains an alpha character (eg 211147/C) it gives Run Time Error 13.

ProjNoB is declared as a String
WeekNo is a named range formatted as Text in the spreadsheet.
If I put the same formula in a spreadsheet cell it does not error.

Does anyone know why this only errors in VBA when there is an alpha character in the ProjNoB variable? And how to get around it?


	VB:
	
(ProjEndB) & "," & ProjAdj & "+ SUMIF(WeekNo," & ProjNoB & ", WeekAdj))"), "mm/dd/yyyy") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks!
janie

I want to autofill so I can increase the alpha character. For example:
=A13
=B13
=C13
=D13
=E13
..........?

If I highlight the first two cells and drag, this only reapeats the alpha character (ie., A13, B13, A14, B14...)
Any thoughts?

Hi,

I have found a very useful UDF for removing non-alpha characters from strings.

(See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).


	VB:
	
 
 
Private Function RemoveCharacters(InString As String) As String 
     '
     ' Function UpCase(Instring As String) As String
     ' Excel 2003 Power Programming with VBA
     '   by John Walkenbach
     '
     ' RemoveCharacters(InString)
     ' Function modified 01/29/2007 by Stanley D. Grom, Jr.
     '
    Dim intLoopCounter As Integer 
    Dim intStringLength As Integer 
    Dim intASCIIVal As Integer 
     
    intStringLength = Len(InString) 
    InString = LCase(InString) 
     
    For intLoopCounter = 1 To intStringLength 
        intASCIIVal = Asc(Mid(InString, intLoopCounter, 1)) 
        If intASCIIVal >= 97 And intASCIIVal

I would like to check if the first three characters are alpha, if so,
remove them. The alpha characters will be any combination.

EX.

BEFORE - ABC123
RESULT - 123

The alpha will not be the same or I would use:
=IF(LEFT(A1,3)="ABC",RIGHT(A1,LEN(A1)-3))

Thanks in advance for ANY assistance.

--
TimE
------------------------------------------------------------------------
TimE's Profile: http://www.excelforum.com/member.php...fo&userid=2139
View this thread: http://www.excelforum.com/showthread...hreadid=483637

My Excel spreadsheet has a column with room numbers, i.e. 101, 102, 2A, 2B, etc. When merging these numbers into a Word document, the room numbers that include alpha characters are completely ignored as if they do not exist. The room numbers such as 2A, 2B, etc. are unrecognized in merging simple data and in "IF" statements. I have tried formatting my cells to General, Text, Number (even using the formula x1), and creating a Special format. I do not get any error messages and everything works fine with the regular room numbers. Any help would be greatly appreciated. Thanks!

Hi everyone, first post here. I registered because I couldn't find an answer in the many threads that deal with this topic, I apologize if this has been covered before.

I have a set of alphanumeric entries and I want to extract only the alpha characters into a new cell. This is the format:

HA1612
UTA345
BCUF19012

I would like the entries in the new cell to be the following:

HA
UTA
BCUF

There are always at least 2 alpha characters and no more than 4 alpha characters in length. The numerical characters range from 1-7 characters in length.

**Also, the alpha characters are ALWAYS before the numerical characters**

I would like some assistance in creating a formula that would tackle this problem. Unfortunately macros are not an option.

Thanks in advance!

I would like to check if the first three characters are alpha, if so, remove them. The alpha characters will be any combination.

EX.

BEFORE - ABC123
RESULT - 123

The alpha will not be the same or I would use:
=IF(LEFT(A1,3)="ABC",RIGHT(A1,LEN(A1)-3))

Thanks in advance for ANY assistance.

I have a successful formula that sums a series of cells that meet certain criteria. It works fine as long as all the characters in the cell series are numeric. Occationally one of the cells doesn't meet a particular criteria and it posts an alpha charater that results in a ##### #value error in the sum total. Is there some way to sum a series of cells and have the formula ignor any alpha character and still sum the rest?

I was hoping if anyone knew how to write a formula to return a value if a character is contained in a cell. The character can be one of may other characters, as long as it is found in the cell.

any help will be much appreciated.

Thank you
Adam

I am working on a spread sheet that needs to use the VLOOKUP function. Does this work with Alpha characters? Example:
=VLOOKUP(FALSE,J24:L28,3,FALSE)

In this statement I am looking for a match to the word FALSE. This is being used with multiple option buttons

Hi,
Can someone please assist in providing the VB code to remove non-alpha characters in a cell so that the remaining text can be placed in a different cell as a single alpha string? For example, how do I convert "Tiger Woods" to "tigerwoods" or "John Arrow-Smith" to "johnarrowsmith"? As you can see from the example I also want to convert any upper case characters to lower case.

Any advice gratefully received.

Cheers.

Does anyone know how to remove all non alpha characters in a SQL statement?

Hi all,

Does anyone have a little snipet of code to convert all alpha characters in the selected cells to superscript. I have the add-in "ASAP utilities", but this only does the last character, regardless of whether it's an alpha or numeric character.

Many thanks,
Mike

In A1 to C3, I have the following entries:
1X 1A 3A 4K 2A 3G 2T 4L 1AB

All of the entries are alpha numeric. They all begin with a number (1 to 4) which is followed by a single or double digit alpha character (A to AB).

I need to count the alphas (each alpha), add the values for each alpha and find the average (sum/count) of the values for each alpha. For example, regarding "A", the formulas would find:
Count = 3
Sum = 6
Average = 2

Any help with the count, sum and average formulas will be greatly appreciated.

-- DJ

All,

I have a macro which sends data out to a .csv file. My
problem is that I need each line
of the .csv file to have a unique four digit alpha
character starting with AAAA and working all the way to
ZZZZ. I have no clue how to do this.

I have a spreadsheet containing weather data - the cells containing
windspeed and wind direction contain a data flag in addition to the
numeric value, example 90S for wind direction and 2.1S for wind speed.
I wish to get rid of the alpha character so I can work with the numeric
values.

How do I parse each cell to remove the alpha character(s)?

--
Lram

------------------------------------------------------------------------
Lram's Profile: http://www.excelforum.com/member.php...nfo&userid=238
View this thread: http://www.excelforum.com/showthread...hreadid=476500

How can I prevent a user from entering a alpha character into a numeric cell,
or how can I make a cell only accept numeric (e.g. calendar date) characters?

Currently my Excel spreadsheets show numbers fort the column headings. I
want to go back to alpha characters (a, B, C, etc.); but, I can't find a way
to change the headings. Please help!!

My Excel spreadsheet has numbers in the column headings. I can't find a way
to change them back to alpha characters.

I need a formula that will add column C values based on column A.
One total for all Column A values that begin numeric, one for values that
begin alpha. I tried the following formla but the alpha characters are
throwing it off.

=SUMIF(AA9:AA13,"<9000000",AC9:AC13)

A B C
1 12AD10 Joe $200.00
2 140000 Sue $300.00
3 16X516 Steph $100.00
4 E09004 Jim $50.00
5 E74997 Lisa $80.00

Results should be $600 and $130

Hi all;

I've got a LOT of numerical data in Excel workbooks to process and work
with.

The individual sheets contain 12-15 columns and 500-2000 rows or mostly
numerical data. However, many of the individual cell values have
alpha-characters as footnotes, e.g.: cell B12 might have the value "5.0a".
In addition, a lot of the cells have a "less than" comparitor, e.g.:
"<5.0", and "<5.0a"

I can deal with the "<" easily enough by using a global find-replace,
replacing it as a negative sign, and treating the vlaue as a negative
number. I can also do a global find-replace for each letter in the
alphabet, and replce the character with nothing.

But is there an easier way to get rid of the alpha-character footnotes than
using 26 find-replace statements? Is there a function or command or
statement that can get rid of all alpha-characters without having to call
out each one?

Thanks a heap,
-jbb

--
J.B.Bobbitt

I have a spreadsheet containing weather data - the cells containing windspeed and wind direction contain a data flag in addition to the numeric value, example 90S for wind direction and 2.1S for wind speed. I wish to get rid of the alpha character so I can work with the numeric values.

How do I parse each cell to remove the alpha character(s)?

I have the following formula:

=IF(ISERROR(LEFT($L22,FIND("'",$L22)-1)+((MID(L22,FIND("'",$L22)+1,LEN($L22)-FIND("'",$L22)-1))/12)), 0, LEFT($L22,FIND("'",$L22)-1)+((MID(L22,FIND("'",$L22)+1,LEN($L22)-FIND("'",$L22)-1))/12))

This formula looks at a particular cell, in this case cell L22, and takes a value that is shown in FT & fractional inches and converts it to the decimal ft equivalent. Ex: 10' 8 3/8" will return a value of 10.6979. I can then use this value for all my calculations.

The problem is that some people have a habit of typing the exact same value above but in the following format: 10'-8 3/8". Notice the "-" shown to show seperation from ft and inches. Unfortunately, if this is typed in this format, the formula above will see this as a minus calculation and the value returned is 9.3021 which obviously creates disasterous calculation problems.

Is there a way to modify the original formula to ignore the "-" character OR if the "-" character is typed in the cell an "ERROR" value will be returned instead?

Thanks in advance.

CheddarThief


No luck finding an answer? You could always try Google.