Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Autofill Alpha characters

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?


Post your answer or comment

comments powered by Disqus
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 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 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,

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

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

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

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.

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?

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.

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!

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

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 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 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.

Does anyone have an easy way to remove alpha characters in a alph/numeric string. I deal a lot with "bills of material". They concist of reference designators in the from below:

C1,C3,C6,C27,C40 or
R2,R6,R70,R120 or
CR2,CR5,CR7,CR40 or as well as different alpha characters. The only thing the strings have in common is that they have the same alpha character before the number as well as a comma seperating them. What im looking for is the following:

C1,C3,C6,C27,C40 to be C1,3,6,27 or
R2,R6,R70,R120 to be R2,6,70,120 etc.

Is there an easy way using formulas or macros to accomplish this. Thanks in advance for the help. Bob

I am comparing to columns of data from MS Project using Excel. In one column are the resource names for the project tasks. In addition to the names are the designations for % utilized. So the column's data will look like this:
John Jones[15%],Mary Smith, Fred Arguello[240%].

I want to compare this column with another resource names column which will never have utilization values - only the names.

In order to do that I need to "mask" the non alpha characters out of the first string.

Any thoughts on how this can be accomplished?

-denise

Flag in an adjacent cell only the text strings below that contain ending alpha characters that are not preceded by space(s). For example, I only want to flag numbers 2.), 4.) and 6.).
1.) 4 P-17093
2.) 2 P-17094A
3.) P-17094 E
4.) P-17094D
5.) 7 P-17095 E
6.) 8 P-17095C


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