Substituting Numbers With IF Function?

Oct 18, 2012

telephone codes in SA begin with the international code of 27 which when followed by a mobile number drops the zero from the Mobile numbers. eg 27722732415 without the SA code should be 0722732415. i want to remove the 27 and substitute it with a 0 ONLY if it occurs at the beginning of the number and not when it occurs in elsewhere within the telephone number

View 8 Replies


ADVERTISEMENT

Substituting Values From A List

Nov 18, 2006

Master List:

AA1/AB1-Bailey
AA2/AB2-Johnson
AA3/AB3-Madison

JA1/JB1-Parker
JA2/JB2-Evans
JA3/JB3-Metz

MA1/MB1-Hill
MA2/MB2-Reynolds
MA3/MB3-Sanders

I run a report that generates a list like this:

I need a way to convert the AA1,AA2,etc. in column B1 to the names they represent, when I paste my report in. The values in B1 vary each time the report is ran and there are 2 values for each name so I need it to always resolve to the right name.If anybody has any ideas,like a macro or formula,

View 9 Replies View Related

Substituting Texts From Specified List

Dec 1, 2009

Got the following data in different cells:

And on a different sheet in the same workbook, have 45 different values to be converted:

What is required?

Knowing that using a VLOOKUP function, i can compare a value in the first cell to the list and return the desired value, i need to substitute the strings of characters with those the the list of 45 values so that i can proceed with my work.

Basically, for the 1st Cell for eg..

2232-2232-

I will need a formula to replace "2232-" by the value which a VLOOKUP function will give when comparing to the list of 45.

Result shall look like below:

20-20-

Looks simple when have the same set of characters in the cell, but for a cell which has multiple sets:

2210-22G1-45R1-45G1-20R1-20GP-
It becomes difficult for me. What i need exactly is:

Formula to substitute using the vlookup function so that it performs a lookup of the value in the specified cell with the list of 45 values and returns the value (20- or 40-) to be substituted in the cell. What i need finally is a set of 20- & 40- replacing the sets of present data.

View 9 Replies View Related

Substituting Text In A Cell For A Worksheet Name In A Formula

Feb 18, 2010

The content of cell "animal!A1" will change according to a simple vlookup table. Let's say the value can be "dog", "cat", or "horse". In cell "animal!A5", I want to duplicate the content either "dog!A5", "cat!A5", or "horse!A5", depending on the current value of "animal!A1".

I've tried to do a simple reference like:

="A1"!A5

or

=A1!A5

wanting the A1 to actually read either dog, cat, or horse so the reference would refer to the worksheet of the same names. This doesn't work, so I need to know if there is a way to do this.

View 9 Replies View Related

Substituting Multiple Values For Data Validation?

Jan 3, 2013

trying to substitute out values that i am using in data validation. From the sentence:

Consulting (Logistics, IT, Management)

I would like to remove the spaces, the (, the ) and the commas so that it reads:

ConsultingLogisticsITManagement

So that i can use it for data validation.

In the data validation box i am trying the formula:

=INDIRECT(SUBSTITUTE(substitute(substitute(substitute(c2," ",""),"(",""),")",""),",","")

But i keep getting told i have an error.

View 6 Replies View Related

Function To Test For Whole Numbers

Jan 22, 2009

I have a resolution calculator that I am working on.

You put your original resolution in say 1440x1080

then below you put in any one of the new target resolutions.

Place 720 in the height and you get a message that says

"960 is your new matching Width resolution"

Id like to follow up on that with an if statment that test to see if the cell is blank if it is then null, if not then test 960 in this case to see if 960/16 = a non decimal number.

So in the cell to the right of that sentence it would return the result
"and 960 is 16 pixel safe" something like that.

I can probably figure out how I will handled the cell arrangement, numbers, and text I just for now need to find a way to do a test on whole numbers and return a text value (my guess is an if statement)

View 6 Replies View Related

Function For A Range Of Numbers?

Jan 22, 2014

I am looking for a function that gives a range of numbers a score, here is what i need:

if A1 is between 6&15 B1=25 if A1 is between 16&35 B1=15 if A1 is between 36&65 B1=5 if A1 is 66 or more B1=0

View 5 Replies View Related

Creating A Function To Sum Numbers

Jan 7, 2010

I am trying to create the function below:

View 3 Replies View Related

Can You Use MAX Function On Numbers/text?

Dec 21, 2009

I am trying to use the "MAX" function to find the max temp on a particular day in a week from 7 separate sheets. The problem I have is one particular company autopopulates their temperature block with the temp and degree symbol: 46°F instead of just 46. The max function is thrown off it appears by the symbol and letter. Is there a way to get the max function to only look at the numbers? I'm also open to the option of autoconverting each temp to a number on the master sheet and letting the "MAX" function search through those if that's possible.

View 10 Replies View Related

Spell Numbers Function

Jan 13, 2008

I had entered a spellnumber function in Excel 2007 and it was working fine but then all the sudden it disappeared. I would prefer a function to convert numbers to text without doing visual basic code.

View 7 Replies View Related

IF Function True Not Recognizing Numbers

Sep 22, 2009

I am working in two different files. If number "40556" on worksheet B is showing on worksheet A, the function is to state "TRUE", but it's stating "FALSE". Both files' numbers are listed as GENERAL under FORMAT CELLS.

View 12 Replies View Related

Limiting Numbers Of Arguments In A Function?

Jul 26, 2009

there is limit of argument in a function that can i put. for example i am not able to figure out why this function does not work

View 2 Replies View Related

Use The Countif Function To Look At That Range Of Numbers For

Jul 18, 2006

I have a range of numbers between 1 and 25 in 50 cells.

How could I use the countif function to look at that range of numbers for
values between 5 and 10 and count those values that meet the criteria of
between 5 and 10?

Or if there is another function to use, let me know.

View 11 Replies View Related

How To Use VLook Up Function To Look At A Range Of Numbers

Oct 26, 2012

is it possible to use the VLook up function to look at a range of numbers - I.e.

30-40, answer = 15%, I have tried 30-40 also 30,40 but none seem to work?cualte?

View 4 Replies View Related

Sum Numbers For Each Month By Sumif Function

May 4, 2007

Column A have random dates and column B have numbers. How can sum numbers for each month By sumif function?

View 6 Replies View Related

Excel 2010 :: Random Numbers In One Function

Jul 22, 2012

I'm currently working on my masters dissertation and am using excel 2010.

Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.

I'm sure there has to be a faster way than creating 1000 tables for each random number generated.

Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.

View 2 Replies View Related

Incrementing Serial Numbers With Send Key Function?

Oct 3, 2013

I am trying to get Excel to write a series of incremented serial numbers to an external program.
I need 10 per line, then for the code to send ENTER and start writing on the next line. Following is my current code:

For i = 0 To qty - 1
SendKeys prefix_sn & next_sn + i & ","
Next i

View 6 Replies View Related

Excel Function For Highest Number For Every 10 Numbers?

Dec 11, 2013

Suppose I have a column filled with numbers in fraction. And correspondingly I have a specific name for every 10 numbers. I want to find out which is the maximum among the 10. For this I could use the "MAX" function in excel. Similarly I have to find the maximum among the next 10 numbers. Since I have about 200 such numbers under 20 different names and eight such sheets (10 numbers per name), it becomes difficult to type the formula and select the range everytime. I would like to know if there is any shortcut for getting this highest number among the 10 numbers.

Eg.
Name Value Number
TU 489.662.272
TU 380.450.0379
TU 331.970.0475
TU 309.641.004
TU 300.890.1215
TU 288.840.0501
TU 280.090.022
TU 278.580.0115
TU 270.160.0397

[code]....

View 6 Replies View Related

AverageIf Function To Average A Range Of Numbers

Sep 15, 2009

I'm trying to use the average if function to average a range of numbers, only if a different range of numbers numbers equal a number in two different cells.

ex. average A1:F1 if G1:L1 equals M1 or M2

View 3 Replies View Related

Function: Constantly Adds The Numbers In A Column

Jan 25, 2009

I don't think there is such a function but is there a way that I can have a function that constantly adds the numbers in a column and when the sum of 240 is reached it adds 30. I need it to do this only once and not keep adding at every 240 point.

View 2 Replies View Related

Excel 2010 :: Random Numbers In One Function?

Jul 22, 2012

I'm currently working on my masters dissertation and am using excel 2010.

Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.

I'm sure there has to be a faster way than creating 1000 tables for each random number generated.

Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.

View 1 Replies View Related

Create String Function With Range Of Numbers

Sep 11, 2012

Is it possible to create a string with a range of numbers

1
3
4
5
6
8
10

Result: 1,3:6,8,10

Or

1
2
3
4

Result: 1:4

Or

1
3
5
7

Result: 1,3,5,7

Where consecutive numbers are separated by a ":" and other numbers by a ",". I use the following function to create the ","-concatenate, but I don't know how to create the ":"-ranges.

Function AConcat(a As Variant, Optional Sep As String = "") As String
'By Harlan Grove, March 2002
Dim Y As Variant
If TypeOf a Is Range Then
For Each Y In a.Cells

[Code] .........

View 9 Replies View Related

Using Large Function To Sort Column Of Numbers

Dec 17, 2012

I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.

I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.

I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?

Name
Net
Gross

All Sort
SortA
SortB
SortC
SortD

A
508
-200.129

101.685

[Code] ..........

View 6 Replies View Related

Difficulty With IF Function For Cells Containing Both Numbers And Letters?

Feb 1, 2013

I have a sheet with part numbers, costs and wish to add varying number to the cost in each row, based on a certain aspect of the part number:

A
B
C

[Code].....

In the example, 01/1X/2X/3X are the differentiating aspects of the part number. To rows in column A containing 01 (preceding the dash) I want to add nothing, 1X+.75,2X+1.5,3X+9.

I have researched the nested IF formula and can use it in a test as long as column A contains either numbers or letters. The problem I have is because there is both numbers and letters, the logical argument won't validate "if true". Here is my formula (omitting my attempts with parenthesis etc. around the letter in the logical argument):

=IF(IF(A1=1X,B1+.75,IF(A1=2X,B1+1.5,IF(A1=3X,B1+9,B1)))

View 9 Replies View Related

RAND Function To Generate Phone Numbers

Oct 24, 2007

How can I use the RAND function to generate a list of simulated phone numbers.

View 9 Replies View Related

Function That Distributes Numbers Depending On The Quarter And Year?

Jun 10, 2014

I'm trying to find out a function that allows me to evenly spread the amounts given a certain start year and quarter and end year and quarter. Let's say, I have 20 dollars and the starts in 3Q/2012 and ends in 2Q/2013. Then, the money should be spread out in four quarters. If the end is in 3Q/2013, then the spread is five quarters, and the amount distribution automatically changes.

Attached is my spreadsheet.

View 4 Replies View Related

Limit On Number Of Random Numbers In Prob() Function

Mar 23, 2009

I'm using the prob() function, and it works for no more than 61 random numbers. Does anyone know of a limit on the number of random numbers used in the function, and whether it's possible to use more numbers in the function? My formula is:

=PROB($C$11:$C$71,$L$11:$L$71,G11,H11)

View 11 Replies View Related

Function To Count Numbers Excluding Empty Cells

Jan 6, 2010

Is there a function that will count a column of numbers where that will eliminate counting empty cells based on a certain date that will also capture any numbers that is added after refreshing the table from Access? I have attached a spreadsheet for an example. I need to count on the number in column V that equals 13 for the date of 12/22/09.

View 5 Replies View Related

Match Function - Database To Record Drawing Numbers

Jan 9, 2012

I have made a database to record drawing numbers.

In column A is the drawing number and in Column B is the revision number.

I am trying to write a macro that will alert the user when they are trying to enter a drawing that contains BOTH the same drawing number AND the same revision number as one already in the database.

Since drawing numbers will be reused and only revision numbers changed when a revision is entered, I need the function to check both column A and Column B then display a msgbox if there is a match.

View 1 Replies View Related

Extract Numbers From String And Run Arithmetic Function Using Excel VBA

Aug 6, 2012

I have a column A with following values below:

Column A
"VL50s"
"M50s"
"H50s"
"VL50s"
"H50s"

I would like to extract the numbers and run the following arithmetic function below into column B.

key:
x is a number
VLx --> (x) + 1
Mx -->(x) + 2
Hx --> (x) + 3

the output should look like the following using the key above:

column B
51
52
53
51
53

View 9 Replies View Related







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