truncating data within a cell for an entire column

Hi,
I have a numeric value in a cell that is followed by a space and then three 
alphanumeric characters e.g., -24 dBm. How can I delete the alphanumeric 
chracters and preserve the nummeric charaters so that I can apply the average 
function to the entire column?
Thanks,
vlapi
0
vlapi (1)
4/18/2007 3:26:00 PM
excel.newusers 15348 articles. 1 followers. Follow

3 Replies
476 Views

Similar Articles

[PageSpeed] 30

vlapi wrote:
> Hi,
> I have a numeric value in a cell that is followed by a space and then three 
> alphanumeric characters e.g., -24 dBm. How can I delete the alphanumeric 
> chracters and preserve the nummeric charaters so that I can apply the average 
> function to the entire column?
> Thanks,
> vlapi

Try the text to columns feature under the Data menu.
You will need insert a blank column to the right if one doesn't exist.
Its pretty straight forward but if you need more info just post back.

gls858
0
gls858 (460)
4/18/2007 4:10:26 PM
gls858

Just for info...............

At third step in Text to Columns you can click on any column you don't want
pasted and select "do not import(skip)"

In this particular case it would eliminate the need for inserting a blank column
to the right.


Gord Dibben  MS Excel MVP

On Wed, 18 Apr 2007 11:10:26 -0500, gls858 <gls858@yahoo.com> wrote:

>vlapi wrote:
>> Hi,
>> I have a numeric value in a cell that is followed by a space and then three 
>> alphanumeric characters e.g., -24 dBm. How can I delete the alphanumeric 
>> chracters and preserve the nummeric charaters so that I can apply the average 
>> function to the entire column?
>> Thanks,
>> vlapi
>
>Try the text to columns feature under the Data menu.
>You will need insert a blank column to the right if one doesn't exist.
>Its pretty straight forward but if you need more info just post back.
>
>gls858

0
Gord
4/18/2007 5:22:04 PM
Gord Dibben wrote:
> gls858
> 
> Just for info...............
> 
> At third step in Text to Columns you can click on any column you don't want
> pasted and select "do not import(skip)"
> 
> In this particular case it would eliminate the need for inserting a blank column
> to the right.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Wed, 18 Apr 2007 11:10:26 -0500, gls858 <gls858@yahoo.com> wrote:
> 
>> vlapi wrote:
>>> Hi,
>>> I have a numeric value in a cell that is followed by a space and then three 
>>> alphanumeric characters e.g., -24 dBm. How can I delete the alphanumeric 
>>> chracters and preserve the nummeric charaters so that I can apply the average 
>>> function to the entire column?
>>> Thanks,
>>> vlapi
>> Try the text to columns feature under the Data menu.
>> You will need insert a blank column to the right if one doesn't exist.
>> Its pretty straight forward but if you need more info just post back.
>>
>> gls858
> 

Good point. I usually keep the data just in case. I can always delete 
the column later if need be. Just a habit of mine.

gls858
0
gls858 (460)
4/18/2007 7:15:08 PM
Reply:

Similar Artilces:

Data Refresh Error
I queried a database using microsoft query in Excel - Data - Import External Data - new database query. After running my query, I then tried to refresh the data by performing the following steps;Data - Refresh Data, at that poing I get the following messgae [Microsoft][ODBC SQL Driver]Protocol error in tds stream. And the data would not refresh. How do I resolve this error ...

Is the CRM Data Migration Framework compatible with CRM 1.2?
I am attempting to use the MS CRM Data Migration Framework to move data into CRM via the CDF staging database. We are running version 1.2 of CRM and I am attempting to use the Data Migration Framework that is currently available on the Microsoft Partners page. When I attempt to run the CDF Initialization tool (Microsoft.Crm.DataMigration.InitializeCDF.exe, which is tagges as version 1.0.3107.0) I receive a message stating that the version of the CDF database differs from that of the CRM databases. I can see the CDF database in SQL Server Enterprise Manager, so the Migration Framew...

After compaction
Hi everyone! After standard compacting I have some emails shortage, sent emails from date1 to date2 have disappeared. I do not have the bakup and wondering if there is a chance to get this back? Any help much appreciated, Tomek Your system is? Mail is OE 6? DAS To reply directly replace 'nospam' with 'schmetterling' -- "tob" <informatyka@mzk.zgora.pl> wrote in message news:u0Pnl0irKHA.4284@TK2MSFTNGP04.phx.gbl... > Hi everyone! > > After standard compacting I have some emails shortage, sent emails from > date1 to d...

Change Column Name
Can anybody advice me on how to go about change the column name in the worksheet. Instead of currently the column name is A, B, C I would like to change to for example A="Date" , B ="Name", and so forth to be shown on the worksheet. Thanks. LSB, It can't be done. You can change it to "1, 2, 3, etc." as opposed to "A,B,C, etc" by selecting R1C1 reference style. Suggestion......... Use column headers in the first row. If you want, you can get rid of the row and column headings altogether (meaning that your first row/header row will be all that...

using subtotals with other data to calculate
I need help!! I'm trying to use the subtotals of one worksheet to paste into another to calculate out costs but when I paste them over, I get all of the cells including the subtotals. I only want the subtotals & to be able to match them to another group of data for calculations. Can anyone help me? Doug To copy just the subtotal amounts: 1. Collapse the Subtotals, and select the range of cells that you want to copy. 2. Choose Edit>Go To, click Special 3. Select Visible cells only, click OK 4. Choose Edit> Copy 5. Select the cell where the paste should start 6. Choos...

Given xsd files, need to send XML with data
Hi, I have been given a set of xsd files which I need to use to generate XML files with my data added. I am using the DataSet.ReadXmlSchema() but get the error: System.InvalidOperationException: Nested table 'Contact-Address' which inherits its namespace cannot have multiple parent tables in different namespaces. The two questions I have are 1) Is this the best approach? 2) What generates this error? I have reduced the xsd files to two example files: <?xml version="1.0" encoding="UTF-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchem...

How to pick a biggest data from a group of data?
19590709 27.81 28.65 27.81 27.81 27 571 19590710 27.42 27.60 27.30 27.42 92 676 19590710 27.57 27.75 27.44 27.57 119 874 19590710 27.66 27.77 27.51 27.66 105 544 19590710 27.80 27.87 27.65 27.80 19 577 19590713 27.70 27.80 27.38 27.70 64 663 above is a segment of my data,as you can see,the first col is the date,each raw of data is a group of data,and there are several groups of date are generated at the same day(1959-7-9) I want to pick the "valid date" out each day,and I compare them with data at the last col of each group at the same day,the gr...

Gantt Bar truncated
Quick search ends in no joy.... I had a fellow scheduler drop buy with a question. He has inherited a Project (2003) file. The task bars in the gantt chart view do not reflect the "start" and "finish" dates in the start and finish column. When the dates are added to each end of the bar in the gantt chart view, the task bar extends from the start date but does not stretch all the way out the planned finish date. I did a cursory view of the file on my way out and there was nothing readily apparent that would be driving this condition. I'm new to MS Project...

split contents in one cell into two cells
i have data in a microsoft word document that is 2 long columns, seperated by a space. when i copy and paste this into Excel, it copies it into 1 column with a space between the numbers. how can i split this one column into 2 different columns? Hi egoldwyn Use Data>Text to columns in the menu bar -- Regards Ron de Bruin http://www.rondebruin.nl "egoldwyn" <egoldwyn@discussions.microsoft.com> wrote in message news:78393FA1-FC1D-45DE-9BB9-C580884592FF@microsoft.com... >i have data in a microsoft word document that is 2 long columns, seperated by > a space. wh...

How can I send an e-mail including data from a spreadsheet?
I have customer's data in a Excel Spreadsheet, I want to send an e-mail to each customer including the data by customer. Is there a way I can perform this work?. Thanks in advance for any help! Hi jcfv Do you have the data from each customer in a seperate sheet or do you have it in one sheet.? -- Regards Ron de Bruin http://www.rondebruin.nl "jcfv" <jcfv@discussions.microsoft.com> wrote in message news:EE9AE059-7F67-46D4-B201-55085FFADCBD@microsoft.com... >I have customer's data in a Excel Spreadsheet, I want to send an e-mail to > each customer includin...

data validation
Hello, I'm trying to use data validation to restrict the text allowed in a cell. I want to allow the cell to contain any combination of 7 specified characters. If the permitted characters are for example - a,b,c,d,e,f,g then 'a' is allowed, 'gddf' is allowed, 'gga' is allowed etc etc, but 'abx' is forbidden. I can't use a list of permitted characters in the data validation spec. because the number of combinations is huge. I might be able to use a formula involving 'SUBSTITUTE' but it's messy. Any ideas please? Thanks KK Hi try using a...

Data Validation #10
I have a worksheet that contains three columns of data. All of the data are people's names and some are repeated in multiple columns and some are not. What I'd like to do is use a function to generate a fourth column that contains a list of individuals from those three columns without containing any repeats or omitting any individuals. SO. MARK JONES TIM CAROTHER MARK JONES ANN MAKER ANN MAKER ALBERT SMITH Would turn in to: MARK JONES ANN MAKER TIM CAROTHER ALBERT SMITH I know how to do this with a single column using a countif function, I just cant s...

Automatic Data Entry Script OnChange Event for Date Field.
Hi, Sub: Automatic Data Entry Script OnChange Event for Date Field. has anyone assigned 'Today date'( new Date()) to a custom date filed on the form - OnChange of a Picklist field, I have the following code. DatefiledName.value = new Date(); This assigns, currect date to the field, but then when I save it does not like it. When I manually assign a date it displays in the format mm/dd/yyyy, and saves correctly, so I changed the script to: d= new Date(); s = d.(getMonth() +1); s += '/' + d.getDate(); s += '/' + d.getFullYear(); DatefiledName.value = s; This also acc...

Paste data from AJ to K
I would like to create a macro that performs the following steps but I am unsuccessful. Can you help me? 1. Check Column AJ for data starting in Row2. 2. If Column AJ has data check the same row of Column K for data 3. If Column K is blank paste the data from the same row of Column AJ into Column K Hi, How about this Sub Copy_Rows() Dim LastRow As Long Dim MyRange as range, C as range Set sht = Sheets("Sheet1") LastRow = sht.Cells(Cells.Rows.Count, "AJ").End(xlUp).Row Set MyRange = Range("AJ2:AJ" & LastRow) For Each c In MyRange ...

Reports and Budget not loading data
Shortly after I switched from Money 2005 trial to Money 2006 trial (my trial had lapsed so I went back to the website to download the the full version and pay and could no longer get Money 2005, so I downloaded the 2006 trial for another few weeks) Budget and Reports both failed because the August Budget is not functioning. I can still generate past months Budgets but August just gives me the Budget figures and will not load actual data. I can still look at September's expected Budget but I have no idea if data will load. Is there any way to fix this? Presumably related to this ...

Cell Formatting #15
I am trying to take and copy a format in a cell to another cell, but when I do that it want to change the vaules to include the cell next to it. Example: Cell D5 =A1+B1+C75 when I copy & paste in D6 I get =A2+B2+C76 I want D6 to be =A2+B2+C75 How can I do this? Take a look at "The difference between relative and absolute references" in XL Help: Then try: =A1 + B1 + $C$75 In article <055F2AE3-819D-46B9-9C64-116C1CC8A333@microsoft.com>, "jmaulsby" <jmaulsby@discussions.microsoft.com> wrote: > I am trying to take and copy a format in a cell...

Naming the columns
I'm so new to computers. I've entered my data and sorted it. Now I need to label each column. Take out the A,B,C, etc. and give them names like Last Name, First Name, Teacher, Grade, etc Can someone help me, please! You can't. Put your headings in the first row(s). -- Regards; Rob ------------------------------------------------------------------------ "TheresA" <anonymous@discussions.microsoft.com> wrote in message news:2BC3811C-1786-4734-A125-E5A6BC53CC44@microsoft.com... > I'm so new to computers. I've entered my data and sorted it. Now I need ...

blank epay data
I use MSMoney 2005 epay to pay bills. A transaction has just cleared my bank in the number range used by MSMoney but has blank information. Looking at MSMoney, I can see where I used epay for check numbers #5908 and #5910. The phantom transaction is #5909 and it is totally blank, except for the money amount, date and check number To make matters worse, #5008 was the last epay transaction I performed on my old machine, and #5910 is the first known transaction on my new machine. I copied the files from the old machine to the new machine. After I discovered this problem I went back...

Data collection
Hi, we are new in GP10 and collecting data. our manufacturing style is on piece work basis and every piece have spacific labor cost, can some body explain me the relation between work centers, routings, operations and labor? we want the record of each work centers, operations and labor keeping in mind we don't have permanent labor in manufacturing. Malik, Not that easy! its a huge process, I would suggest checking "MfgProductionFunctions.pdf" in printable manuals for explanation about your question and the other manufacturing PDF's (Starts with MFG) for any other q...

insert memo feild into a memo field truncates at 255 characters -
Greetings I have a table that has a memo field to retain specific information - text - which is the base of the combo box. Via a combobox on a form this memo field is added to a forms field which is also sized as a memo called "Project_Notes" - this part works. At a certain time the user uploads this form and its feilds to a parent table which also has a memo field to recieve the data from the other memo field. 'we have created the new entry now we should update the notes strOtherFields = ",Action_By,To_Do_date" _ ...

accessing outlook data using excel macro
Hey all, I'm very new with all of this, however, my problem is this: I have a bunch of data in excel which I then want to compare to certain "task" fields in outlook (ie. the "Due Date" column in Tasks for example), then update those outlook fields with new data. I was able to do it in an Outlook macro, but not in the Excel macro. I get a "User defined type not defined" error when trying to define a namespace variable as "Outlook.Namespace" Any help would be greatly appreciated. Thanks. ...

copy data where exact match
I have two spreadsheets. Spreadsheet 1 has reference numbers R1 to R100 in column b and goin down the rows. Spreadhseet 2 has reference numbers R1 to R100 in column b and goin down the rows. Colums b-h contains the same column names in both spreadhseets bu different data. I want to be able to click on a button on spreadsheet one next to on of the rows (one button for each row and therefore, each reference) an for it to load spreadsheet two and for the button to find an exac match on spreadsheet two and copy the data from spreadhseet one on t spreadhseet two, overt the existing data for ...

Line styles for Cell Borders
I would like to know if there is a way to add to or alter the available line styles for cell borders that appear in the Fomatting window. The number of styles is vary limited. I would like to be able to alter or add to the thickness of the lines and the types of lines shown. Is there a way to easily do this? Carl Not that I know of. You could always try putting a transparent rectangle over the cell, but I don't see a large number of options there (maybe 6?) for the line styles. On Wed, 1 Dec 2004 19:45:01 -0800, "Carl" <Carl@discussions.microsoft.com> wrote: ...

Can you help me chart this data?
I cannot find a way to chart this data - can anyone make suggestions: The data represents each of our servers by component ID, age, End of service life, failures, MTBF, Maint cost and business unit. The corresponding values are scored - the goal is to identify assets to be tech refreshed by thier score, ie. if a component has a lot of failures and it is really old, we may decide to tech refresh. ID Age EOSL Failures MTBF Maint Cost BU C102204 7 12/31/2010 4 688 £5,460 Corporate Score 3 10 3 ...

count cells in autofilter
while working in an excel spreadsheet, i use the autofilter tool to sort data. on some spreasheets it will list the total number of items found from the autofilter (ie., 10 of 15 records found. How do i get this to display automatically each time? If your list has many formulas, the Status Bar might show "Filter Mode" instead of a count of the visible records. You can use the Subtotal function to count the visible rows. The Status Bar may also show 'Filter Mode' if a cell in the list is changed, after a filter has been applied. The following MSKB article has inform...