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

Free Microsoft Excel 2013 Quick Reference

Vlookup? text and numbers in the same cell

I have table that contains values that have both text and numbers in the same cell example: “Non-GMP;T-201__1049;T-201;38;22649”. I need to be able to search these cells for the lookup_ value, in this case 22649 then compare it to the table array and find the corresponding col_index value. I tried Vlookup but that seems to only work with single text or numbers string in a cell.


Post your answer or comment

comments powered by Disqus
I've figured out how to put a formula and text in the same cell, thanks
to this group. However, I can't figure out how to format the value of
the cell. For example, I want to create one cell with the following:
Cost $XXX.

The following is what I put in the cell:
="Cost $" & B21*8.

This is the result I get:
Cost $35.32365

I want the value to round to 2 decimal points, but I can't figure out
how to do that. The cell B21 is already in currency form, but the new
value does not show up with a dollar sign. So, I guess I have 2
questions:

1. How do I get this dollar sign to show up automatically (being that
the value is based upon another dollar value?)

2. How do I round the value to 2 decimal places?

Any help is greatly appreciated.

Thanks!!!

Hi everyone,

Who can tell me how to read the column which include text and number in the excel via ODBC?
like one columnA include 'abcd'(Text) and 1234(number)
I want to read all the data in columnA, but seems that I can only read the data which the datatype is consistent with the begining of the data in the column.
For example. (please correct me if anything is wrong)
If the begining of the data are 'adcd', 'dfd-yrtd','yreh4645', the data type in this column was defined as text by default, and then I can't read the numeric data in this column if there is numeric (like 1123) data in the column.

I use the sql like: select * from ...
So I think I should read all the data in this column.

Thanks
Winnie

I am looking to create an excel sheet that can be saved as a txt document, which in turn will run a dos-based program. I need to repeat this .txt-creation operation severl hundred times, so I'm looking to make it as simple as possible. What I'm looking to do seems like it would be simple to me, but I can't figure out how to do it.

I am looking to type a single piece of text (for example "joseph") into a cell and hit return and have that SAME cell read a number(ie the number of characters of the entered word), space, and the text I just entered. (for example "joseph" should return "6 joseph" in the SAME CELL.

I know how to do this operation in ANOTHER cell (one that refers to cell H37, for example):
=LEN(H37)&" "&H37

But it is important that when I save it as a .txt file, that I have no extraneous cell values that appear on the .txt document. A DOS-based computer program is run off this .txt file. Therefore I am looking to do this simple text manipulation operation in the SAME CELL that I first enter the text into.

Any ideas?

I want to be able to have a number and a word in the same cell, but still be
able to use formulas to add, subtract, ect. to the number.
For example: I have 6 meters in cell A1 and want to add 11.9 meters

This has probably been asked before, but I couldn't find the answer, so here goes:

Is it possible to show the data from a certain cell and then add text to that in the same cell.

Let's say I want to show the data from C13 and then continue on in that same cell with some text. Let's say 'blahblah'. How can I do that?

I can get it to give me the time but it will come up 0's in the date. As shown

A1 B1 C1
01/03/05 02:00 PM 01/03/05 02:59 PM 01/00/00 00:59

What I want it to do is show the same date ( i.e. 01/03/05) and minutes
using this function.(=TEXT(MOD(B15-A15,12),"hh"" hrs ""mm"" mins""") in the
same cell.

Also, if it runs into the next day I want to put time restrictions on it as
well. (i.e. the
work time ends 7pm and resumes at 8am)

I have figured out how to get the times Using this function too (b1-a1=c1)
gives the minutes, but I want to show the date as above shown B1, but I keep
coming up with what is shown in C1 above.

can any one help?

Hello ... I need your help with this ... I'm writing a list of steps IN THE SAME CELL. For example:
1.- Step 1
2.- Step 2
3.- Step 3
and so on.

But, I need you to tell me how to make this "numbering list" IN A SAME CELL, and that the numbers be aligned automatically on the left side of the cell (as in the example above). At this time, I have to fill with spaces to get this "left alignment" .

Maybe exist a key combination for making it, or something like that.

Thanks a lot for your help!!!

I can get it to give me the time but it will come up 0's in the date. As shown

A1 B1 C1
01/03/05 02:00 PM 01/03/05 02:59 PM 01/00/00 00:59

What I want it to do is show the same date ( i.e. 01/03/05) and minutes
using this function.(=TEXT(MOD(B15-A15,12),"hh"" hrs ""mm"" mins""") in the
same cell.

Also, if it runs into the next day I want to put time restrictions on it as
well. (i.e. the
work time ends 7pm and resumes at 8am)

I have figured out how to get the times Using this function too (b1-a1=c1)
gives the minutes, but I want to show the date as above shown B1, but I keep
coming up with what is shown in C1 above.

can any one help?

I am working on a project plan template for my department that incorporates predecessor relationships The row number of a deliverable is its ID number and the =row() function works very well right now. column B of the spreadsheet contains the values of all the predecessors. In the event during a template change I need to add a row, all deliverables automatically update the new row number of their preceding task. However, if a deliverable has more than one predecessor, I have to type these in manually and they do not auto update, which can create quite a headache.

My dept. has asked that this be a VBA free worksheet so anyone can edit or modify it, as very limited number of people understand VBA. I am rather inexperienced myself with VBA, but can get by with a bit of help.

At any rate, is there any function or array that would return multiple row numbers to the same cell, regardless of whether they are consecutive. This function could most likely not be an array that would prevent rows from being moved, cut, deleted, etc.

I would also be open to creating an ID field rather than basing the values on row numbers if that gives me more options. I cannot think of any formulas that would return multiple values without actually performing some sort of mathematical operation or turning it into a true / false evaluation, such as and/or if.

Any help would be appreciated!

Thanks!

Hi all,

Just wondering if it is possible to add numbers which are in the same cell, seperated by commas, for example

A B
1 Person 1 10, 10, 15
2 Person 2 10
3 Person 3 10, 10
4 Total 65

I know I could just put each number in a different cell, but for some reason the powers that be don't want to do this

Thanks

Hi, is it possible to have text & a date in the same cell?? In A! I have
todays date =today(). In D1 I want the statement, "Balance at (today's
date).
I have tried =concatenate("Balance at" , & A1) but it returns a
numerical value for the date bit???
Cheers
Paul.

--
tallpaul
------------------------------------------------------------------------
tallpaul's Profile: http://www.excelforum.com/member.php...fo&userid=8453
View this thread: http://www.excelforum.com/showthread...hreadid=518014

Hello,

Can anyone help me, I wish to return the higest number in a string of
numbers (all in the same cell) seperated by commas.

Thanks

I want to round a cell with an existing formula in it.

this is the formula: =+'Revenue Projection'!M1 and it shows as $8,541,403 (it is a result from another sheet)

I want to round that number in the same cell to the nearest $1000 ($8,541,000)

Anybody know how to get this done?

Hi, is it possible to have text & a date in the same cell?? In A! I have todays date =today(). In D1 I want the statement, "Balance at (today's date).
I have tried =concatenate("Balance at" , & A1) but it returns a numerical value for the date bit???
Cheers
Paul.

I am trying to find out if you can add multiple numbers within the same cell?
Like you are able to do with lotus

HI,

Please help how to add numbers when text is in the same cell.

264 Days 6876.50 Days 435.50 Days 3169 Days 10745 Days 7 Days 10781 Days

I am sure this question has been asked in the past but i cant find the answer using the search function.

I am trying to figure out how to put a value and text in the same cell when the value in the cell is used as a variable for a formula in another cell.

For example: cell b3 has a value of 6 and cell c3 is multipying the value in b3 with the value in another cell. I would like to know how to add text to cell b3 and still have the value in that cell used in the formula in cell c3

Any help would be appretiated.

How can I add a number with text in the same cell excel? Example:

A1 = 100 KB
B2 = 200 KB

C3 = Want to equal 300 Kilo

Thanks

Hi,

I have a cell say N1, in which I am supposed to write something which will look like this:

Now the number 5421 is generally a sum of series of cell, for example:
For now I calculate the sum in a temp. cell and then manually write the sum (5421) in the N1 cell's formula bar.

Now how can I do the same without using the temp cell i.e. can I use a Text and a Formula together in the same cell?

Thanks

I would like to have text and a functioning hyperlink in the same cell in
Excel.

Hey All, I am working on a worksheet template that creates a list in which the length is controlled by data input into a cell. One of the columns of the list contains a Vlookup formula to load data in the cell automatically. The cells in the column also contain a drop down list.
Okay, everything works fine. If I set a list length to a certian number and fill in the data, using the drop down list on some cells and filling the others in automatically, it works great. Now if I make the list smaller, the cells in which I set the data through the drop down list remains filled with old data. It appears they lose the formula part of the cell and only the drop down remains.
Is this clear at all? Is there any way to overcome this prediciment?

TIA
Keith

btw instead of the old data I would like to revert to the original formula (Vlookup) AND the dropdown list.

How do I add a string of text to a formula in the same cell? Assuming the formula is as follows:
=DATE(YEAR(B7),MONTH(B7),DAY(B7)-1)

and I would like to add text "@ 1200 HRS" at the end of the formula to look like the following:
15/02@1200HRS

I'm new at making good use of excel and it's formula and functions. Really hope someone can help me

I am trying to create a worksheet, that references word documents in the same cell with vlookup responses for more info.

Example:
(the items are in a validation list)

{ item a } { item a's basic summary } { link to word.doc with full item a info. }

{ item b } { item b's basic summary } { link to word.doc with full item b info. }

When I select item b from the list, I'm trying to make it so it's summary will be shown, and in another cell next to it, a hyperlink will take you to the item b word.doc.

Using the same cells, when I select item a, I'm trying to get item a's summary, and in the same cell as the one used by item b's hyperlink, I would like it to change to match item a's info.

I'm relatively new to Excel, and I've been frustratingly stuck on this for awhile. I searched the forums, so I hope this isn't a repeat question.

Thank you for yor assistance!!

xmortal

Try something like this:

Select the impacted cells
Format>Cells>Number tab
Category: custom
Type: ddd mm/dd/yyyy
Click the [OK] button

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

"sharkey" wrote:

> I need to show the day and the date in the same cell
>
> tue 27/02/06
> wed 28/02/06
>
> can anyone help please
>


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