A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

underscore in functions



 
 
Thread Tools Display Modes
  #1  
Old June 25th 08, 06:19 AM posted to microsoft.public.excel.worksheet.functions
westronwynde
external usenet poster
 
Posts: 2
Default underscore in functions

Hi, I hope this has not been asked and I just can't find it. I am having
trouble with underscores in Excel functions. A certain program I use
(industry specific so I can't name it here) exports data into Excel. Most of
the time the data comes over as text. However sometimes I get formulas and
formatting such as this (=_393-_394-_395-_396) Needless to say, this formula
is not helpful and I need to find a way to get rid of this formatting without
having to copy and paste values into another worksheet. Essentially the
formula should look like this =SUM(D4-E4-F4-G4)
Ads
  #2  
Old June 25th 08, 08:50 AM posted to microsoft.public.excel.worksheet.functions
Sam Wilson
external usenet poster
 
Posts: 523
Default underscore in functions

Hi,

I think your export processing is creating names for the cells.

If you click on D4 and look at the white box to the left of the formula bar
I bet it'll say "_393". The export process renames "D4" as "_393" and then
all formulas refer to the new name.

The formulas should all still work though.

Sam

"westronwynde" wrote:

> Hi, I hope this has not been asked and I just can't find it. I am having
> trouble with underscores in Excel functions. A certain program I use
> (industry specific so I can't name it here) exports data into Excel. Most of
> the time the data comes over as text. However sometimes I get formulas and
> formatting such as this (=_393-_394-_395-_396) Needless to say, this formula
> is not helpful and I need to find a way to get rid of this formatting without
> having to copy and paste values into another worksheet. Essentially the
> formula should look like this =SUM(D4-E4-F4-G4)

  #3  
Old June 25th 08, 03:04 PM posted to microsoft.public.excel.worksheet.functions
westronwynde
external usenet poster
 
Posts: 2
Default underscore in functions

Yes, thank you. I realize it (the export) is creating names for the cells. I
need it not to do this or somehow to beable to rename them back to the
alphanumeric indication. The formulas (and cell references) it creates
interfer with the manipulation of the data. Say I need to enter an
alternative calculation and I have 700 rows of information. Unless I
specifically type =sum(A1+B1), I will get cell references when I select these
cells (=_364+_365). Now, if I wanted to copy this formula down (i.e. next
formula being A2+B2, then A3+B3, etc.) this is fine to do once but having
mutiple data sources and having to do this even one for eith column is
inefficient. The "underscore" formula does not have extended use and will not
change because the cell references will not change. The function stays
(=_364+_365) and does not become (=_464+_465).

"Sam Wilson" wrote:

> Hi,
>
> I think your export processing is creating names for the cells.
>
> If you click on D4 and look at the white box to the left of the formula bar
> I bet it'll say "_393". The export process renames "D4" as "_393" and then
> all formulas refer to the new name.
>
> The formulas should all still work though.
>
> Sam
>
> "westronwynde" wrote:
>
> > Hi, I hope this has not been asked and I just can't find it. I am having
> > trouble with underscores in Excel functions. A certain program I use
> > (industry specific so I can't name it here) exports data into Excel. Most of
> > the time the data comes over as text. However sometimes I get formulas and
> > formatting such as this (=_393-_394-_395-_396) Needless to say, this formula
> > is not helpful and I need to find a way to get rid of this formatting without
> > having to copy and paste values into another worksheet. Essentially the
> > formula should look like this =SUM(D4-E4-F4-G4)

  #4  
Old June 25th 08, 04:04 PM posted to microsoft.public.excel.worksheet.functions
Sam Wilson
external usenet poster
 
Posts: 523
Default underscore in functions

Ah, I see. YOu can remove all names in a selected range with the following:

Sub DeleteName()

Dim strName As String

For Each cell In Selection
strName = cell.Name
ActiveWorkbook.Names(strName).Delete
Next cell

End Sub

But that may well knacker all the existing formulas.

"westronwynde" wrote:

> Yes, thank you. I realize it (the export) is creating names for the cells. I
> need it not to do this or somehow to beable to rename them back to the
> alphanumeric indication. The formulas (and cell references) it creates
> interfer with the manipulation of the data. Say I need to enter an
> alternative calculation and I have 700 rows of information. Unless I
> specifically type =sum(A1+B1), I will get cell references when I select these
> cells (=_364+_365). Now, if I wanted to copy this formula down (i.e. next
> formula being A2+B2, then A3+B3, etc.) this is fine to do once but having
> mutiple data sources and having to do this even one for eith column is
> inefficient. The "underscore" formula does not have extended use and will not
> change because the cell references will not change. The function stays
> (=_364+_365) and does not become (=_464+_465).
>
> "Sam Wilson" wrote:
>
> > Hi,
> >
> > I think your export processing is creating names for the cells.
> >
> > If you click on D4 and look at the white box to the left of the formula bar
> > I bet it'll say "_393". The export process renames "D4" as "_393" and then
> > all formulas refer to the new name.
> >
> > The formulas should all still work though.
> >
> > Sam
> >
> > "westronwynde" wrote:
> >
> > > Hi, I hope this has not been asked and I just can't find it. I am having
> > > trouble with underscores in Excel functions. A certain program I use
> > > (industry specific so I can't name it here) exports data into Excel. Most of
> > > the time the data comes over as text. However sometimes I get formulas and
> > > formatting such as this (=_393-_394-_395-_396) Needless to say, this formula
> > > is not helpful and I need to find a way to get rid of this formatting without
> > > having to copy and paste values into another worksheet. Essentially the
> > > formula should look like this =SUM(D4-E4-F4-G4)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
'underscore' line in '__ / __' not printing ??? akm Excel Discussion (Misc queries) 2 December 11th 07 09:36 PM
How can I double underscore in excel? KennyG Excel Worksheet Functions 2 December 20th 06 08:46 PM
Splitting 1 cell into 2 based on underscore in text RJF Excel Worksheet Functions 5 May 9th 06 07:50 PM
how can i make a measurment with and underscore under the second diane Excel Discussion (Misc queries) 4 April 1st 06 07:25 AM
underscore between name in excel csv format to export names into o Biochemist Excel Worksheet Functions 3 May 17th 05 07:11 PM


All times are GMT +1. The time now is 07:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.