ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   underscore in functions (https://www.excelbanter.com/excel-worksheet-functions/192530-underscore-functions.html)

westronwynde

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)

ExcelBanter AI

Answer: underscore in functions
 
Hi there! I'd be happy to help you with your issue regarding underscores in Excel functions.

Underscores are often used in Excel functions to separate different parts of the function or to make the function easier to read. However, in some cases, underscores can cause issues, especially when importing data from other programs.

To convert the formula with underscores to a more readable format, you can use the SUBSTITUTE function in Excel. Here are the steps:
  1. Select the cell containing the formula with underscores.
  2. Click on the formula bar at the top of the screen to edit the formula.
  3. Replace all underscores with a different character that is not used in the formula, such as a dash (-). You can do this manually or by using the Find and Replace function (Ctrl+H).
  4. Once all underscores have been replaced, press Enter to save the formula.
  5. In a new cell, use the SUBSTITUTE function to replace the dashes with minus signs (-). The formula should look like this:

    Formula:

    =SUBSTITUTE(A1,"-","-"

  6. Press Enter to apply the function. The formula should now be in a more readable format.

Alternatively, you can use the EVALUATE function to convert the formula with underscores to a calculated value. Here are the steps:
  1. Select the cell containing the formula with underscores.
  2. Click on the formula bar at the top of the screen to edit the formula.
  3. Replace all underscores with a plus sign (+). You can do this manually or by using the Find and Replace function (Ctrl+H).
  4. Once all underscores have been replaced, add an equal sign (=) to the beginning of the formula.
  5. Press Enter to save the formula.
  6. In a new cell, use the EVALUATE function to convert the formula to a calculated value. The formula should look like this:

    Formula:

    =EVALUATE(A1

  7. Press Enter to apply the function. The formula should now be converted to a calculated value.

Sam Wilson

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)


westronwynde

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)


Sam Wilson

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)



All times are GMT +1. The time now is 05:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com