# Returning Numerical Characters In A Cell

Feb 2, 2014

i am trying to return the numerical characters from a cell so in A1 i have 6-4 3-6 7-5

A2 i would like to return 6

A3 4
A4 3
A5 6
A6 7
A7 5

there are also possibilties that A6 & A7 could b 2 digit numbers such as 10-12 or could be 9-11 or 11-9 etc

ADVERTISEMENT

## If Statements: Non-numerical Characters Returning False When True

Jun 24, 2009

I have a formula in column A of the following sheet. The problem is that when the entry in cell H contains a letter as well as a number then the formula in column a returns 0.

Also if the value for false in the forumula is changed from 0 i.e to 1 or 2, then a value is returned in column A (this is shown in the red highlighted cells).

AssetRegexample.xls

Does anyone have any idea why this is?

## Returning A Numerical Value From Text

Oct 25, 2013

I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!

Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?

## Returning Only Numerical Values

May 11, 2009

it seems that excel is deciding what type of array this is by evaluating the type of the first cell in each column and applying this type to the rest of the array's column. Problem is that i want all values to be brought in, text and numerical.

Dim VArray As Variant
Sub DA201()
If FileThere(Path & "DA201.xls") Then
GetDataTowers Path & "DA201.XLS", "", "DataRange", Sheets _("Towers").Range("CStart"), False, False
Else
MsgBox ("File DA201.xls not found. Click OK to continue")
End If
End Sub

Public Sub GetDataTowers(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim n As Integer
Dim row, m As Integer

If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else....................................

## Returning Characters After A Series Of Numbers

Dec 10, 2013

I have a list of data in Column A of a spreadsheet that is all at different lengths.

I would like to return the numbers from this (see below) seperately and also everything after the last number so returning "green"

Example:

EAST MIDS, YORKSHIRE0112012green
EAST MIDS, YORKSHIRE0112012red
EAST MIDS, YORKSHIRE0112013black
EAST MIDS, YORKSHIRE0112013brown

## Returning A Number Of Characters From A Variable String

Jun 4, 2014

If for example, I have a set of IDs like:

Bellen-234-B25R
Carlota-345-C20RDF
Quad-765-Q20LP

How would I return just the first 4 characters after the second dash? The closest I can get is ALL the characters after the second dash.

## Excel 2010 :: Delete Cell Contents If Cell Contains Numerical Value?

Oct 8, 2013

Recently I have been practising writing my own macros in VBA. I am using Excel 2010.

I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.

I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using..

## Text In A Cell To A Numerical Value

Jan 20, 2009

Basically what I am trying to do is build a formula but I have hit a wall it seems like.

what I want to do to complete the formula is, I have 2 names in mind that I want to be able to enter one cell and have excel translate them into a number. So for example if I enter the name John in that cell excel will know that John means the number 3 and then tomorrow I erase John from that cell and enter Jim and excel will know that Jim means the number 2.

But again I want to be able to enter a certain body of txt into one cell and have excel recognize it as a number. How can this be done?

I have Excel 2003 and 2007

## Status Formula (find Out The Formula Where Numerical Valued Between 1-2000 Equals True And Numerical Under 0 Equals False)

Dec 12, 2009

I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.

## Display A Msgbox For Numerical Value Of Each Cell

Dec 6, 2009

-In cells J6:P11
- Display a MsgBox for the value in J6; Then
- Display MsgBox for the value in K6; Then
- Display MsgBox for the value in L6
- etc, etc

## Count Or Sum Of Non-numerical Cell Content

Apr 23, 2006

I'm working with a fairly large spreadsheet, and need to find out how many times a specific word or numerical value comes up in a column (for instance, I might need to know how many times 'Anderson' comes up in column B). Every time I try to use the sum, dsum, sumif, count, dcount, or countif functions, however, I keep coming up with either 0 or the #VALUE message. This is very frustrating, as you can well imagine. I could try to sort the data by the various terms and values I need and then physically count everything but this strikes me as time-consuming, prone to error (we're talking a few thousand entries, here), and something Excel should theoretically be able to do anyway if only I knew how to ask it.

## If Cell Contains Text, Return Numerical Value

Jun 16, 2007

I need a formula that looks at a Cell and if it contains any text at all it will return a numerical value into a seperate cell. I have done this before but for some annoying reason i cant remember how i did it.

## Give Letter Numerical Value And Use It To Format Another Cell?

Jul 22, 2014

I am a teacher and I want a column to turn a particular colour if a pupil has met their target grade and another if they have not. Our grades work as letters A* being the best then descending as follows A, B, C, D, E, F, G, U.

I have a column with the pupil's target grade (one of the above letters) and another column with their current grade. How do I get excel to understand the ranking of the letters and format the current grade column according to whether they have met their target or not?

## Multiple Numerical Formats In A Single Cell?

Oct 7, 2008

In a single cell, I would like to show 2 values: a currency and a percentage. So A1 and A2 has values, say 50000 and .10 respectively (these are derived via formula, if it's of any relevance). What I ultimately want is "\$50,000 and 10%". I've originally thought about adding the "\$", " and ", and "%" separately, but I need the currency value to include commas.

## Giving A Text Cell The Value Of A Numerical Cell

Mar 20, 2008

I need to know if you can give a cell with text (such as a name) the value in a different cell (eg a total value cell).

Basically when the name is displayed I can add that cell to a team and therefore easily calculate a total teams worth by dragging names into the team's column.

## Inserting A Numerical 1 Or 0 Into A Cell Depending On The Values Of Two Other Cells

Jul 24, 2008

I'd like help writing a single formula that assigns either a numerical 1 or 0 to a cell based on values from two other cells.

By way of background, I'm working with three columns. Cells in column F contains values from 1 to 4. Cells in column G contain values from 5 to 11. Cells in column H are currently blank.

I need a single formula that will insert either a numerical 1 or a 0 into column H according to the following conditions.If F3 = 1 and G3 = 5 or 6 or 7 then let H3=1 otherwise let H3=0
If F3 = 2 and G3 = 7 or 8 or 9 then let H3=1 otherwise let H3=0
If F3 = 3 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
If F3 = 4 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0

## Single Cell Two Numerical Values - Multiply In Different Columns

Jun 27, 2014

I am looking for formula , single cell two numerical values , multiply in different columns

example :

Cell (A1) contain: =350,23+25,59 (two values in a single cell)

request:

in (B2) only first value so 350,23 multiply (x) by fixed number &in (C2) only second value so 25,59 multiply (x) by fixed number

## Take X Characters From One Cell And Y Characters From The Next?

Aug 1, 2014

So we have people's names in a table. First name in one column and last name in another column. We have a 3rd column where we can use 8 characters to do a combination of First 5 of Last Name + first 3 of first name. However, if someone's last name is only 3 or 4 characters, we'd then want to take more from the first name to fill out the 8 characters.

So:
Charles Johnson -> Johnscha
John Smith -> Smithjoh
Willian Wu -> Wuwillia

What's the best way to do that without creating some crazy formula with tons of if/thens?

## Excel 2010 :: Clear Chevron Characters And Characters Inside Chevrons

Jan 17, 2012

Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.

Mouse, Mickey ;

Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).

## Cell Returning #n/a

Mar 29, 2008

i have this formula in cell e28 =IF(B28<>"",LOOKUP(B28,STOCK!A1:A500,STOCK!B1:B500),"") and when its empty it returns a blank cell

but in cell e29 i have exactly the same formula =IF(B29<>"",LOOKUP(B29,STOCK!A1:A500,STOCK!B1:B500),"") but i returns #n/a

why is this and how do i stop it i want the cell to be empty unless there is a code entered in b29 in which case it will look up the relevant info for me

## Looking For Value In Column Then Returning Value From Different Cell In That Row

Apr 23, 2014

Basically, I want to use look in one cell to get a value then in a table look in a coloum to find the same value then return a value from a different column in that row.

This is to organize groups of children participating in an event.

See the attached spreadsheet for details.

Groups example.xls‎

## Returning The Value Of The Larger Cell

Feb 5, 2009

I am looking to return the larger value of 2 cells into a seperate cell.

## Empty Cell Not Returning Zero

Jul 30, 2009

I had this problem a few days ago and NVBC suggested Text to Columns, which worked initally but didn't seem to 'save' or stick... also, I'm not sure what is causing the problem to begin with. It was my understanding that Excel couldn't return a truely 'blank' value. If a cell was blank or empty, Excel still 'sees' it as Zero. WHY then, for the Love of Gummi Bears, will Excel not calculate my formula. It's a simple formula!

=A13+B13

I've tried
=SUM(A13+B13)

I've tried
=IF(ISERROR(A13+B13),"",A13+B13)

But if don't TYPE in a 0 or a number, then it just does nothing. 50 + 0 = should be 50 not blank or #VALUE!. 50 + Nothing = should be 50 not blank or #VALUE!

Attached is a small copy of what I'm trying to do. I've double checked that all number cells are formatted to numbers, not text. I tried the Data:Text to Columns trick. I've tried Tools:Options:Show Zeros. I've tried Manual and Automatic Calculation. They shouldn't have to type in 60 cells of Zero to get the simple calculation.

## Returning 0 From Blank Cell

Jan 7, 2013

I'm linking one sheet in a workbook to another. The formula is simple,

='Link 1'!E1

If there is data in the cell, it replicates it which is what I'm after. However if there is no data in the cell it returns a "0", not what I want at all.

## Returning Cell With A YES Or No Calculation?

Nov 11, 2013

I am trying to return a cell with a simple yes or no answer and having a little difficulty.

I have entered the following formula which is returning #NAME
=IF(E4<=1.25,ORH4<=0.01,yes)
Calculation needs to be as follows:

Install Cost <1.25 and Profit atleast 0.01
The YES NO needs to be in the Invite? column

## Sum Returning Blank Cell

May 28, 2009

I have a current formula using SUM formulas obviously in the answer cell the answer is returning 0 due to no data inputed, how do i make the cell show empty.

eg. =SUM(A1*A3) returns answer 0 need cell to be blank.

## Returning Cell Reference

Dec 10, 2007

I have a sheet containing collums with month an weeks. I try to find out which collum the first week start and where te last week the same mont ends.

Let say A5:E5 is filled with december in each cell. The row below (A6:E6) says week 48, week 49, week 50, week 51, week 52.

I want a formula telling me de Cell reference where desember begins en where it ends. In my Excample (A5 and E5). If Possible, I also could prefer that the cell reference was to rows below start/end. In my example (A7 and E7)

## Returning The Address Of A Cell

Jun 27, 2009

i'm trying to figure out a way that would return the address of the cell that contains the highest/lowest value of a range i.e. =cell("address",min(g3:g6)).

if the lowest value was in cell g4, i'd want the output from above formula to be \$g\$4.

## VLookup Without Returning Cell In Same Row?

Jun 24, 2013

view the graph below:

A
B

1
EXTENSION 101

2
INBOUND TALK TIME
55M19S

3
OUTBOUND TALK TIME
1H22M13S

[code].....

I am trying to create a formula that will act like a vlookup but will return a different row. In other words, On page two I need to write a formula that says: In column 1 if you see "Extension 101" return column 2 of the row right beneath it, so on and so forth. So page two should return a mirror of what page one says, the difference is I would like people to type in "Extension 101" in a cell and it would return all the date.

A
B

1
EXTENSION 101

2
INBOUND TALK TIME
55M19S

[code].....

Cell A1 would be typed in//Cell A2-A4 would be static//Cell B2-B4 would return these numbers based on info from A1.

Cell A1 would always be unique in column A (on both pages).

## Looking Up Max Value In A List But Returning A Different Cell

Mar 4, 2009

The conversion rate is updated daily from telephony reports, and I manually sort it into conversion order each day before sending it round.

What I want to do is find the highest converter for each team.

So:

Column A ('TeamLeader' range) has the Team leaders in, column B has the agent name ('Agent' range), and column F has the agents conversion rate ('conversion' range).

I think I'm on the right track with this formula:

=(MAX(IF(TeamLeader="Bonnie",Conversion))

But this only displays the figure in the Conversion rate, how to I get it to 'step back' 4 column's and show the agents name?

I tried this:

=VLOOKUP(MAX(IF(TeamLeader="Bonnie",Conversion)),A5:F60,-4,FALSE)

but that returns #NA - is this because I'm asking the VLOOKUP to look 'backwards' 4 columns?

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