Do a calculation in cells with text data format

I have a few columns of cells having a mixed data format of number and text. 
Is it possible to convert the first row of numbers in text data format for 
further calculation? Your guidance to accomplish it is appreciated.

Thanks,

Ray 


0
12/12/2005 2:37:39 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
500 Views

Similar Articles

[PageSpeed] 24

Example?

-- 

Regards,

Peo Sjoblom

"Ray" <NoSpam-ZQLi@GMail.com> wrote in message
news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
> I have a few columns of cells having a mixed data format of number and
text.
> Is it possible to convert the first row of numbers in text data format for
> further calculation? Your guidance to accomplish it is appreciated.
>
> Thanks,
>
> Ray
>
>


0
terre081 (3244)
12/12/2005 3:17:36 PM
Peo,

Below is an example.

A1                                A2        A3
67                                78        =A1+A2
Will be discontinued.

"67" and "Will be discontinued." are on two rows of one cell.

Your advice is appreciated.

Ray

"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
> Example?
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
> "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
> news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
>> I have a few columns of cells having a mixed data format of number and
> text.
>> Is it possible to convert the first row of numbers in text data format 
>> for
>> further calculation? Your guidance to accomplish it is appreciated.
>>
>> Thanks,
>>
>> Ray
>>
>>
>
> 


0
12/12/2005 3:28:12 PM
If you use alt-enter to force that new line in the cell, you could use:

=--LEFT(A1,FIND(CHAR(10),A1)-1)

The =left() portion returns the value as a string.  The -- converts that string
to a number.



Ray wrote:
> 
> Peo,
> 
> Below is an example.
> 
> A1                                A2        A3
> 67                                78        =A1+A2
> Will be discontinued.
> 
> "67" and "Will be discontinued." are on two rows of one cell.
> 
> Your advice is appreciated.
> 
> Ray
> 
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
> > Example?
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
> > news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
> >> I have a few columns of cells having a mixed data format of number and
> > text.
> >> Is it possible to convert the first row of numbers in text data format
> >> for
> >> further calculation? Your guidance to accomplish it is appreciated.
> >>
> >> Thanks,
> >>
> >> Ray
> >>
> >>
> >
> >

-- 

Dave Peterson
0
petersod (12004)
12/12/2005 4:02:24 PM
Dave,

Many thanks for your suggestion that works correctly provided the cell does 
have two rows.  It not, it does not work.  As the cell may have one or two 
rows, is it possible to check the condition before applied your statement?

Ray

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:439D9F10.AD38A7EC@verizonXSPAM.net...
> If you use alt-enter to force that new line in the cell, you could use:
>
> =--LEFT(A1,FIND(CHAR(10),A1)-1)
>
> The =left() portion returns the value as a string.  The -- converts that 
> string
> to a number.
>
>
>
> Ray wrote:
>>
>> Peo,
>>
>> Below is an example.
>>
>> A1                                A2        A3
>> 67                                78        =A1+A2
>> Will be discontinued.
>>
>> "67" and "Will be discontinued." are on two rows of one cell.
>>
>> Your advice is appreciated.
>>
>> Ray
>>
>> "Peo Sjoblom" <terre08@mvps.org> wrote in message
>> news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
>> > Example?
>> >
>> > --
>> >
>> > Regards,
>> >
>> > Peo Sjoblom
>> >
>> > "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
>> > news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
>> >> I have a few columns of cells having a mixed data format of number and
>> > text.
>> >> Is it possible to convert the first row of numbers in text data format
>> >> for
>> >> further calculation? Your guidance to accomplish it is appreciated.
>> >>
>> >> Thanks,
>> >>
>> >> Ray
>> >>
>> >>
>> >
>> >
>
> -- 
>
> Dave Peterson 


0
12/12/2005 4:22:52 PM
One way is to just "add" that line feed in the string you're searching through:

=--LEFT(A1&CHAR(10),FIND(CHAR(10),A1&CHAR(10))-1)

If the cell already has one (or lots), it won't hurt.  If it doesn't, then the
formula will find the one we added.

Ray wrote:
> 
> Dave,
> 
> Many thanks for your suggestion that works correctly provided the cell does
> have two rows.  It not, it does not work.  As the cell may have one or two
> rows, is it possible to check the condition before applied your statement?
> 
> Ray
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:439D9F10.AD38A7EC@verizonXSPAM.net...
> > If you use alt-enter to force that new line in the cell, you could use:
> >
> > =--LEFT(A1,FIND(CHAR(10),A1)-1)
> >
> > The =left() portion returns the value as a string.  The -- converts that
> > string
> > to a number.
> >
> >
> >
> > Ray wrote:
> >>
> >> Peo,
> >>
> >> Below is an example.
> >>
> >> A1                                A2        A3
> >> 67                                78        =A1+A2
> >> Will be discontinued.
> >>
> >> "67" and "Will be discontinued." are on two rows of one cell.
> >>
> >> Your advice is appreciated.
> >>
> >> Ray
> >>
> >> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> >> news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
> >> > Example?
> >> >
> >> > --
> >> >
> >> > Regards,
> >> >
> >> > Peo Sjoblom
> >> >
> >> > "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
> >> > news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
> >> >> I have a few columns of cells having a mixed data format of number and
> >> > text.
> >> >> Is it possible to convert the first row of numbers in text data format
> >> >> for
> >> >> further calculation? Your guidance to accomplish it is appreciated.
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Ray
> >> >>
> >> >>
> >> >
> >> >
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12004)
12/12/2005 6:16:43 PM
Dave,

Excellent!  Thanks a lot.

Ray

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:439DBE8B.2F2E1345@verizonXSPAM.net...
> One way is to just "add" that line feed in the string you're searching 
> through:
>
> =--LEFT(A1&CHAR(10),FIND(CHAR(10),A1&CHAR(10))-1)
>
> If the cell already has one (or lots), it won't hurt.  If it doesn't, then 
> the
> formula will find the one we added.
>
> Ray wrote:
>>
>> Dave,
>>
>> Many thanks for your suggestion that works correctly provided the cell 
>> does
>> have two rows.  It not, it does not work.  As the cell may have one or 
>> two
>> rows, is it possible to check the condition before applied your 
>> statement?
>>
>> Ray
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:439D9F10.AD38A7EC@verizonXSPAM.net...
>> > If you use alt-enter to force that new line in the cell, you could use:
>> >
>> > =--LEFT(A1,FIND(CHAR(10),A1)-1)
>> >
>> > The =left() portion returns the value as a string.  The -- converts 
>> > that
>> > string
>> > to a number.
>> >
>> >
>> >
>> > Ray wrote:
>> >>
>> >> Peo,
>> >>
>> >> Below is an example.
>> >>
>> >> A1                                A2        A3
>> >> 67                                78        =A1+A2
>> >> Will be discontinued.
>> >>
>> >> "67" and "Will be discontinued." are on two rows of one cell.
>> >>
>> >> Your advice is appreciated.
>> >>
>> >> Ray
>> >>
>> >> "Peo Sjoblom" <terre08@mvps.org> wrote in message
>> >> news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
>> >> > Example?
>> >> >
>> >> > --
>> >> >
>> >> > Regards,
>> >> >
>> >> > Peo Sjoblom
>> >> >
>> >> > "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
>> >> > news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
>> >> >> I have a few columns of cells having a mixed data format of number 
>> >> >> and
>> >> > text.
>> >> >> Is it possible to convert the first row of numbers in text data 
>> >> >> format
>> >> >> for
>> >> >> further calculation? Your guidance to accomplish it is appreciated.
>> >> >>
>> >> >> Thanks,
>> >> >>
>> >> >> Ray
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
12/14/2005 5:05:16 PM
Reply:

Similar Artilces:

Superscripts in referenced text objects
Dear, Several days ago a question appeared in this NG on the superscripts in referenced text objects. The answers correctly recommended looking for sub-/superscript font sets. What does it mean? Optimally such a font set should contain at least all the 34 alphanumeric characters within the second 128 ANSI locations as both kinds of those scripts. Has anybody ever come across such a product? I succeeded several months ago, after the recommendation of Bernard Liengme, in adopting Chemistry fonts (“Chemistry SansSherif” and “Chemistry Sherif”). They offer unfortunately only numerals in wa...

how do justify text in linked textboxes without hyphernation
I am trying to do a newsletter with linked text boxes. When I paste an article into the boxes I can not get them to fit properly. What I get is words hyphernating next to the edge. I would like to justify and get the words fitting in. How can I do this Do you have the paragraphs set for justified? If you want to control the hyphenation manually, Tools - Language - Hyphenation - Clear "Automatically hyphenate this story" -- JoAnn Paules Microsoft MVP - Publisher How to ask a question http://support.microsoft.com/kb/555375 "Naida" <Naida@discussions.microsoft...

How do I display a formula calculation in plot area of a chart?
I want to add a text box in the chart that returns the value from a formula that was entered on the data page. The formula value is not related to any series value. WiseGuyOne: You need to build your text expression in a cell, then insert that expression to the chart. First, to build your text expression: 1. Select your cell 2. Enter = first to let Excel =know you are adding a formula 3. You can now enter text within quotes and cell values .. here's an example =" Yesterdays average temperature was " & sheet1!$a$3 Note that the & is used to add ...

What is the cell data reference for 5th row and 7th column called
Can someone help me with this question? I need to get the answer for my homework. Thanks for your help. I'm guessing, but might it be G5? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Shy-di@verizon.net" <Shy-di@verizon.net@discussions.microsoft.com> wrote in message news:A0979494-228B-435E-98F7-6BD26C900266@microsoft.com... > Can someone help me with this question? I nee...

Editing external data via ODBC
Hi, I'm in the process of transitioning an excel/access (2003) based solution to a web-based one. Right now, we have a dozen or so separate spreadsheets and a couple of access databases that get updated several times a day. I've managed to import most of the data into a mysql db which I've tried to normalize as much as possible. This will be the backend db for the webapp. I've set up an ODBC connection from excel to the db and it works like a charm; a few query tweaks and I can reproduce the data from all the separate spreadsheets. But now, how about going the other way? ...

language date formatting
My PC is regional setting is dutch I have to print documents with date format dd-mmm-yyyy Problem is, that mmm should always be English (not dutch). How to enforce that? The only way I can think of is to write a custom VBA function to return the month abbreviation. UNTESTED sample function with no error handling follows Public Function fEnglishDate (DateIn as Variant) Dim dateString as String If IsDate(DateIn) = False Then fEnglishDate = DateIn Else Select Case Month(DateIn) Case 1 : "Jan" Case 2: "Feb" ... ...

Report generated from text box fields
hello. i have a 3 column report. The 3 products compared on the report are assigned when the report opens and asks the user for the ID.. is there are way to create 3 unbound text fields and have the report compare the IDs of the products listed in the text box.. i have done something similar that searches based on what was entered in a text box, but wasnt sure how to have the report pick up three different text boxes.... this is an example of what i have in another application that opens up a query Like "*" & [forms]![SEARCHMENU]![text37] & "*" -- Message po...

Cell Contains Data.
How can I tell a cell that IF a cell contains data (numbers, text, whatever) do x or y. How can I identify data?? I don't want to tell the function to look for a specific number or text, just data, just something in the cell... =IF(A1<>"","Data","No Data") "GEM" <GEM@discussions.microsoft.com> wrote in message news:07916799-F312-4E89-A926-59356B74ED89@microsoft.com... > How can I tell a cell that IF a cell contains data (numbers, text, > whatever) > do x or y. How can I identify data?? I don't want to tell the function ...

Data Validation
I have noticed the "Ignore blank" check box in my Data Validation window (XL2003), but i cannot figure out what it does. Nor can i find an answer in the Help files. Just what does this option do? Thanks, Tonso Ignore Blank: If checked, blank entries are allowed. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Tonso" wrote: > I have noticed the "Ignore blank" check box in my Data Validation > window (XL2003), but i cannot figure out what it does. Nor can i find > an...

Need to separate multiple numbers in one cell
I have multiple number in one cell and i need to somehow put each number into one separate cell for each number: I have : column A row1 2 5 8 10 I want: column B column C column D column E row1 2 5 8 10 How do I do it on Excel 2007? thanks EggHeadCafe - Software Developer Portal of Choice Map Stored Procedure Output To Class Properties http://www.eggheadcafe.com/tutorials/aspnet/2853a1aa-5db7-40d0-9cde-46847fa770ef/map-stored-procedure-outp.aspx Hi, Use Data > Text to columns and specify the delimiter as space ...

Active cell highlight
Hi, I would like to know if there was a way to have an active cell always in yellow or any color. Example: If the active cell is moved around, the active cell will be always in yellow. Thank you in advance maybe.... you may want to try Chip Pearson's Rowliner: http://www.cpearson.com/excel/RowLiner.htm E wrote: > > Hi, > > I would like to know if there was a way to have an active cell always in > yellow or any color. > > Example: If the active cell is moved around, the active cell will be always > in yellow. > > Thank you in advance -- Dave Pet...

copy value in cell above to cell below for a whole column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Can anyone help? Ctrl+D will copydown the value of the top cell to the highlighted cells below. "tarthur" wrote: > I have a spreadsheet that was saved from a report. this report only lists > the employee name once. There is a way to copy th...

How can I remove the previous lock cells and keep the new ones.
Could you please help me?... I locked and protected the cells A1:H20 in the sheet 1 two months ago. However, yesterday I wanted to unlock and unprotect these cells and locked and protected the cells A21:H40 instead. However, for some reason the cells A1:H20 are still lock and protect. How can I remove the previous lock cells (A1:H20) and keep the new cells (A21`:H40) only. Thanks. Maperalia In EXCEL 2007 take the following actions to unlock cells A1 to H20:- 1. Home / Cells group / Format / Unprotect Sheet / enter the password to Unprotect Sheet / OK / highlight cells A1 t...

ComboBox.Text gets reset between event calls
I am trying to make a ComboBox that will AutoResize to the text being displayed. The steps I take are: OnTextChanged(EventArgs e) { base.OnTextChanged(e); ResizeTextBox(); } private void ResizeTextBox() { Graphics CurrentGraphics; int StringWidth; int MinWidth; int MaxWidth; int MyWidth; string thisText; Font thisFont; SizeF thisSizeF; Size thisSize; CurrentGraphics = this.CreateGraphics(); thisText = this.Text; this...

Import of Data into RMS 2.0?
We will be converting to RMS soon. Is there anyway we can import our old sales history(Monthly) into RMS 2.0? Hi newbie. I am not aware of a free utility that will import all data, including sales transaction history to your RMS system. Retail Realm has an Import Tool that will do this if your current system is Microsoft Point of Sale, possibly POS 2009, but it is licensed (meaning you need to pay for it). Otherwise, I believe you are limited to importing items, customers, suppliers, and UPCs as Aliases, no history. HTH..... "newbierms user" wrote: >...

text on document is really small even though it is set at 12 why?
When I am in a word 03 document the text displayed is really small, even though the font size is set at 12. Either that or the page is really big Have you tried changing the zoom rate? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "suzannah" <suzannah@discussions.microsoft.com> wrote in message news:894012CB-69D6-48BC-92DC-54DBAF71CB7C@microsoft.com... > When I am in a word 03 document the text displayed is really small, even > though the font size is set at 12. Either that or the page is...

Adding data from multiple cell and linking to a new worksheet
I have data in a sheet with names and amounts due. Like A 500 B 300 A 600 B 300 C 400 C 200 There is a second sheet named summary. I want the totals of each person to appear in the summary sheet. These should be linked cells and any change in data in sheet 1 should be updtaed automatically Create a PivotTable http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Rashmi" <Rashmi@discussions.microsoft.com> wrote in message news:56DC0F3A-8D60-4FE2-B778-3A07790EFE8A@microsoft.com... >I have data in a sheet wi...

Move cells down to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7...

data available to MS Access
I'm not able to see the multiple entries of a sharepoint item with a mulit-line text column --I can only access the text of the last entry. With other multi-value columns (attachments, single-line text, numbers, etc), I don't have a problem getting the multiple entries. ...

using data from another cell
I would like to type a name in one cell(1) then check in list (different column) if the same name is there, and if it is there get a value of another cell locate in the same row where. Can some one help me? Create your second list and use VLOOKUP as follows: Name is typed in: A1 List of names and values in: C1:D10 =VLOOKUP(A1,$C$1:$D$10,2,FALSE) "leo" <reani1996@hotmail.com> wrote in message news:OiO5fCTWDHA.2268@TK2MSFTNGP11.phx.gbl... > I would like to type a name in one cell(1) then check in list (different > column) if the same name is there, and if it is t...

Data not valid error message
Access02 Have a Report which appears fine. But when you go from DesignView to ReportView it throws error: ~.."the data you entered for this field is not valid"... it doesn't say which field.....I can click thru the message and it reprompts this a few times (probably once per instance of invalid) and then opens fine. it does not do this when the Report opens ; only changing from design view... but it does do it if it is multipage and you need to go to the next page....so needs to be fixed... there's alot of fields and so am wondering how to troubleshoot....hunch is t...

How to exclude cells from a datasource?
Hi, I have an XY chart in excel97 that uses a range of cells for the data source. Every now and then I would like to exclude a XYnode from the chart. Without having to reselt the datasource. Is there a value one can enter in the cell that exludes it from the graph (compare: putting text in a cell excludes it from the SUM() function). For example point X Y a 1 1 b 2 2 c 3 3 Gives a graph with two lines connecting the three points While point X Y a 1 1 b ? ? c 3 3 Should give a graph with one line connecting point a and c. Point b is left out. Or is the...

How do I preset data to auto copy to a certain page
I have a Macro that hides and copies visible cells to worksheet 3, but, I have a certain row that needs to wait till the next page break befor copying so that I don't have to manually adjust them. Moving them manually takes 25 minutes 2-3 times a day, if there is a way it would really save me. ...

convert text to predefined number in single column
Hi all, Greeting to all of you out there! I am a newbie to this forum and now I have a question here. I have a worksheet which contains thousands of records. Within a particular column (column "N") there are single character in the cells throughout the records (eg. "M", "S"). My question is how can I convert from "M" to "1" and "S" to "2" using formula? Kindly advise. Thanks and best regards. Eric --- Message posted from http://www.ExcelForum.com/ Use a help column, =LOOKUP(N1,{"M","S"},{1,2}...

Concatenated text and Leban's Rich Text Control
Greetings All, I was wondering if anyone has run across the problem I'm having with concatenating rtf control contents and line returns. I am using Steven Leban's rich text tool in Access 2007 (as Microsoft's integrated rich text control is pretty much useless if you need to manipulate its contents) (Steven's tool is excellent). I am trying to insert new text into the middle of a rich text field, after formating it as needed, however, I've noticed that the rich text box inserts a line return at the end of my inserted text. I have searched around and have read that rich t...