# VBA Find Cells Containing Numbers Or Alpha

Feb 5, 2007i need to see what cell in the collum is empty. how can i do that in vb?

View 6 Repliesi need to see what cell in the collum is empty. how can i do that in vb?

View 6 RepliesADVERTISEMENT

I have a sheet with thousands of rows of ID's which are made up of a series of letters, numbers, underscores and or spaces. Within the sheet there are numerous duplicates so I need to able extract the numerical element, which is the key data to be able to identify the duplicates.

The numbers I need to extract are mainly 6 and 7 digits long however don't always sit in the same place within the ID. I don't need the 3 digit number sequences, just the 6 & 7 number sequences

e.g

AB_123_456789_Jones

CD_131_7654321_Smith

AB_123_Jones_456789

I've found a formula that strips out all the numbers, however it also included the 3 digit numbers which I don't want. I've also used Text To Columns to break up the ID across a number of columns however that leaves me with 2 and sometimes 3 columns where a 6 digit ID appears

Is there a simple way to extract the number if it's a certain length? i.e. 6 or 7 digits long

Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.

Example:

376

377

421

376AB

376XY

377NC

421GQ

421EF

Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:

376

376AB

376XY

377

377NC

421

421EF

421GQ

In have a worksheet with about 5,000 rows. In column there is a unique identifier for each row, however the unique identifier can take one of 12 different formats, namely:

L1314LAND00001 through to L1314LAND0XXXX

L1314CUST00001 through to L1314CUST0XXXX

L1314COMM00001 through to L1314COMM0XXXX

M1314LAND00001 through to M1314LAND0XXXX

M1314CUST00001 through to M1314CUST0XXXX

M1314COMM00001 through to M1314COMM0XXXX

N1314LAND00001 through to N1314LAND0XXXX

N1314CUST00001 through to N1314CUST0XXXX

N1314COMM00001 through to N1314COMM0XXXX

S1314LAND00001 through to S1314LAND0XXXX

S1314CUST00001 through to S1314CUST0XXXX

S1314COMM00001 through to S1314COMM0XXXX

These unique identifiers won't always appear in sequentiual order. In Cells B2 to M2, I want to be able to determine what the next entry for each of the twelve variations will be, so for example, if the current highest value in column A for the 'S1314LAND' range S131400421, in cell K2, I would want it to display S1314LAND00422, and if the current highest value in column A for the 'M1314CUST' range M1314CUST0036, I would want cell C2 to display M1314CUST0037, so anyone at a glance can see the next highest unique reference for the relevant set, rather than having to go through the unique identifiers in column A manually to determine the next entry in the series.

I'm not really sure where to start, when there can be more than one aplhanumeric entries ending in the same number, e.g. S1314CUST00500 and M1314LAND00500.

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

Option Explicit

Private Function RemoveCharacters(InString As String) As String

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 <= 122 Then

RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)

End If

Next intLoopCounter

End Function

Two requests:

1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?

2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"

I had an "If" formula that would grab my numeric data from one cell and dump it into the corresponding cell depending on my letter code. I had to change my code vales from apha to numeric. I enclosed the attached spreadsheet and highlighted the cells in yellow that are in my formula that don't work any longer. I was hoping someone may have a solution.

View 5 Replies View RelatedI need to find cells containing text (Alpha characters only) in a single column, an return the row(+1 then -1). The column contains Group names (Unknown) , and under each name follows numeric data until the next group name. The cells are not formatted other than BOLD names. I need to exctract the numeric data in groups.

EG.

----

NameA

324

234

65

NameB

345

67

Footer

----

etc.

I have manged using the following code, but this always assumes there is an "e" in the name, but would prefer a more foolproof method. (I used "e" cos it is ok so far, BUT this could change in the future)

Sc = "A"

Sr = "1"

With Worksheets(1).Range(Sc & Sr & ":A500").................

I have another problem with this damn address file. Column H and I have data in them that is often mixed. As shown below, I have used A and B below, but its normaly in Column H and I. I would be greatul if some could write a macro to split the data into the two columns.

Rows 2-8 is what Im presented with. I would like them to look like 11-17

Note that the number in row 8 does not match, so is left for manual intervention....

I have a table that is 94 columns *12 rows each cell has a different number I need to find a way to duplicate the table but with the numbers in each of the cells doubled.

View 3 Replies View RelatedI have a column that looks like the following and I need to add the numbers:

27 skids

31 skids

56 skids

13 skids

The unit "skids" is constant. The answer I am looking for is "127" or "127 skids"

I have two columns of numbers (this week - Column A and last week - Column B). What I need to do is look at the numbers and pull two lists out in Columns C and D. In columns C a list of the numbers that are missing from last week in column D numbers that are missing that were added from the previous week. The two lists consist of about ten thousand rows of data.

******** language="JavaScript" ************************************************************************>

Microsoft Excel - Book3___Running: 14.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

(A)boutA1=ABCD1ThisÂ WeekLastÂ WeekMissingAdded

21002103110311002310271032103210274103310331034104051035103410361055610371036Â

165571038Â Â Â 81040Â Â Â 91055Â Â Â 101655Â Â Â Sheet1Â [HtmlMaker 2.42]

The first 7 digit number is the input to a certain equation. This equation outputs the 7 digit number after the space.

Input (7146127) ==> Equation ( Unknown) ==>Output (2871082)

These are some data that I have ( inputs and outputs). I was wondering if there is anyway to find this equation. I can get more data if that helps in finding the equation.

71461272871082

53820056426572

61070709150530

75833373805331

55466908456008

49264716615511

12387558353260

20013928484175

67975657084961

38006283229553

68973249414920

65179634653276

66971872613128

59646203411273

69785611400836

58284602307740

64723009925464

57930863495088

58816593932906

62736335606616

52119154778580

60043359437317

61921553996320

51507747216899

13197956063818

64210347411256

65399671193442

55656585589500

55148886441637

30850639587521

69081133766387

54139417804940

53408393698534

62310293191095

30512556590168

14514168265124

89078208134056

85177364363577

68714858216023

17584065849687

52691521917548

9840524785846

59268687494601

89374963791119

45989738961270

65040033345415

15061518946940

55392084949214

45103824265242

84181944909073

I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.

What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.

I have a mass of data which look something like this:

table removed

and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.

So basically for example I would want the top row to now read:

table removed

and then apply the same procedure to the other ranges below this.

A

B

C

D

E

F

G

23445566894 Brwn pdc aft

Cat1

45687930596

$5

45687930596 Gld wdget adi

Cat2

23445566894

$6

In the example above, I would like to do a vlookup in column C that matches the digits in column A to those in column F, with the output being the corresponding value in column G. I believe it involves the left function but not sure how to really use it here.

I want to add a letter to the end of value and have it start over at "A" when the cell value changes. For example

My Column contains

BIR1510

BIR1522

BIR1524

BIR1524

BIR1524

BIR1524

BIR1532

BIR1532

BIR1533

I want this in another column

BIR1510A

BIR1522A

BIR1524A

BIR1524B

BIR1524C

BIR1524D

BIR1532A

BIR1532B

BIR1533A

In a column I have data like 2W, 2BM, 4W, 6BM, 10W and 15BM. Question is how can I total all W and all BM. Total for W = 2W + 4W+10W = 16W and total for BM = 2BM +6BM +15BM = 23BM.

What formula should I used to get totals

I have an application where there is a column A with values of F1 through

F83. There is data in columns B,C,D,E,F AND G.

From time to time there is an addition in column A, the value being F1A or F2A etc.

The rows then have to be sorted so that F1A follows F1 and the next row would start with F2 rather than F10.

Attached spreadsheet - the cell highlighted in yellow.

This is the formula I currently have, but it isn't giving me the results I am looking for:

=IF(TYPESVC="CompanyX", COUNT(Companies!I:I), " ")

Basically, IF the Companies tab, last column has 'CompanyX' written in it - then I want to count the number of responses provided in the Q2N column.

Same formula would be copied over to the other cells to the right as well (for Q5, Q6, Q7, etc.)

I'm doing a sort of product codes which are of different character length with some numeric and others alpha-numeric.

A small section of the sort is below.

I want it to end up like this:

4040

4040A

4040B

4040BTO

4040C

4040E

40400

40409

41402

42402

42402A

43400

But the sort ends up like this:

4040

40400

40409

4040A

4040B

4040BTO

4040C

4040E

41402

42402

42402A

43400

In other words, I would like product codes where the last character is a letter to be sorted ahead of product codes where the last character is a digit. The way Excel is normally set up, it sorts in just the opposite way.

I've got a list of reference numbers in column A

IT0001

IT0002

IT0003

And I need to find what the biggest number is without resorting the list.

I have data in column with combination of alpha-numeric like "Member # 308166 RCI Number 4801-60436". can I split this without using copy and paste?

I'm trying to have the macro limit the cell entry to 5 characters beginning with a "P" and the remaining 4 character being numeric. The problem I'm having is that it's not recognizing the requirement for the beginning "P" but only identifies if the length is less than 5 characters. Any idea how I can resolve this?

For PN2 = 43 To 72

If Not IsEmpty(Cells(PN2, 1)) And Left(Cells(PN2, 1), 1) "P" And Len (Cells(PN2, 1)) 5 Then

MsgBox ("The employee number must be 5 characters long begining with P (e.g. P0001), please change accordingly!")

Range(Cells(PN2, 1), Cells(PN2 + 2, 1)).ClearContents

Cells(PN2, 1).Select

End If

Next

I am trying to create a formula (or if VBA is better) where when a name is put into a field the next alpha mumber is created. Have attached example.

View 3 Replies View RelatedSo what i need is a 6 digit alpha numeric random (1000 entries)

so from some searching i figured that it should be...

=MID(A1,1+INT(RAND()*LEN(A1)),1)&MID(A1,1+INT(RAND ()*LEN(A1)),1)&MID(A1,1+INT(RAND()*LEN(A1)),1)&

MID (A1,1+INT(RAND()*LEN(A1)),1)&MID(A1,1+INT(RAND()*L EN(A1)),1)&MID(A1,1+INT(RAND()*LEN(A1)),1)

[Code]....

but when i fill down it auto it to a2 , then a3 etc... how do i stop that?

I might delete out the o O and 0 cause it gets confusing...

Also, it re-randomizes every time I open the file... is there a way to stop it?

I am trying to make a worksheet change the fill color depending on the data in the cells. I have two issues I cannot find the answers to.

View 12 Replies View RelatedI have a log that I need to number starting in cell A1, as follows:

A-0001

A-0002

A-0003

etc.

I've put this formula in A2 to try an increment it by 1, but it just replicates the string in A1:

=LEFT(A1,3)&MID(A1,2,5)+1&RIGHT(A1,2)

with the proper syntax?...

Can't seem to get a VLOOKUP to work and import alpha text such as a person's name. Is there a formula that would do this similar to a VLOOKUP?

View 9 Replies View RelatedI have cells with alphanumeric values. I need to extract the numeric values based on certain criteria and put these values in adjescent rows.

The sheet I am using has 3 rows . Row B contains data. Row C is Amount, Row D is Month, Row E is Action

To put values in Rows C, D and E , I need the data from row B in following criteria.

data in B row is as follows :-

RS 555/STMT DT 170807/BP12/TO PAY MEMBERSHIP FEE

RS.555/STMT 020907/BP12/ OFFER

ANNFEE RS 555/- / STMT 020907 / BP 12 / FIRST TIME

RS595/STMT 0807/BP12/ first time

MEM FEES RS 525 /STMT 120807 / BP12/1ST TIM

criteria for row C

if the cell contains "AMT" or "RS" or "INR" or "AMNT" or "RS."then extract the value till first "/" or "" and put it

under Amount row. ( E.g: "AMT 595 /" or "Inr 595/" or " Rs. 595")

criteria for row D

if the cell contains "stmt" or begins with "0" or endswith "07" then extract the value till first "/" or "" and put it under Month row.

criteria for row E

if the cell contains "BP" or "OP" then extarct the value till first "/" or "" and put it under department row. ( e.g " BP01 /" or "OP 02 ")

A2 cell data = 11 digits alpha-numeric number.

* begin with either "0" or "1"

I want to the formula to look at the 6th and 6th digit (numeric, alpha-numeric, or Alpha) and compare it with the cells under "'PSCCR Queue - Manager" TAB from C4 thru C7.

Below formula is only looking at the first value when comparing.

=IF(A2="","",IF(ISNUMBER(MATCH(MID(A2,6,2),'PSCCR Queue - Manager'!$C$4:$C$7,0)),"ITS","Non ITS"))

I've tried searching the forum here, but have not seen anything like my problem as follows:

I would like to convert the numeric value in a cell to a coded alpha

the numbers 1,2,3,4,5,6,7,8,9,and 0 would be coded as B,L,A,C,K,H,O,R,S,E respectively.

For example, if cell A1, held the value of 5750, the formula in cell A2 would result in KLOE.

The figures in Col A would be no smaller than 3 digits nor larger than 5 digits.

I have columns and rows that are populated with one of three letters: P, S, & T. At the bottom of the column and at the end of the row, i'd like to assign a numeric score to the entire column/row that is based on converting the characters to a numeric eqivelant. P = 3, S = 2 and T = 1. I'm guess a case statement would be used, but not too sure here. So, I'd need the code behind the worksheet as well as the formula(s) used for the score. I've attached an example.

View 3 Replies View RelatedI 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.

##UPDATED## Hello

understanding the IF forumlas to calculate rows with whole numbers in them for the last columns

CBOOK1.xlsxBOOK1.xlsx

I want to create a formula that values if the number in the cell is in the biggest 25 numbers in the column and also check in another column the same thing.If the number is within the 25 biggest from the first column or the second then to give me the number of the cell. I created a formula but it is very large in order to apply for the 1st to the 25th biggest number. The formula is:

IF(AND(or(K16=LARGE($K$16:$K$152,1),I16=LARGE($I$16:$I$152,1)),K16<>0),K16,"") but only for the 1st biggest number of the columns.

I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.

The sheet looks something like this, numbers are all in one row:

7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32

I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.

I have a workbook that has 3 columns (G, H I) with numbers from 1 to 7 in each column. I want to tell excel that if a row has a particular 3 numbers in these cells then change it to a specified number. I want to do this for the entire workbook. Is this possible?

View 3 Replies View Relateda macro finding the max number in col B in each day (hr 0- hr 23) and in col C put a "1" next to it.

View 10 Replies View RelatedI have 300 number groups and each group has 22 different numbers (from 1 to 80).

Can I find the most common 5 or more numbers in these groups by excel, macro or any program?

for example, let us suppose that 4-15-23-36-45 are the most common numbers and these numbers are in 8 groups from 300.

I have a list of numbers in a column and I need to find which numbers

when summed together equal a figure. I have a list of invoice amounts

that I need to match up with payments (the payments are always made for

several invoices so I need to come up with sums of several invoices to

get to this payment amount).

An example would be I have this in the following section (A1:A10):

$17,213.82

$4,563.02

$85,693.42

$1,166.01

$725.90

$580.09

$2,243.75

$240.16

$207.70

$725.90

I need to find which combination of these figures would sum $1,173.76.

Copyrights 2005-15 www.BigResource.com, All rights reserved