Searching for a character when typed next to a number.

Currently need a formula that will:

1. Check if the range of cells is a number
2. If the original if statement is true, it searches for a + symbol next to the number returning X if true and removing 1 from length (to get rid of the + symbol) and a " " if false.
3. If the original if statement is false it returns a " "

This is what I have:

=IF(C7:D7=ISNUMBER,=IF((SEARCH("+",C7:D7)),"X & =LEFT(C7:D7, LEN(C7:D7)-1)"," ")," ")

But something is wrong with the second argument.

Thanks in advanced.

blynx,

Welcome to the forum!
Can you provide some sample data with expected results?

Theres a lot wrong with that formula

=IF(ISNUMBER(C7:D7),IF((SEARCH("+",C7:D7)),"X & LEFT(C7:D7, LEN(C7:D7)-1)"," ")," ")

but it will always come up with error on search("+",C7:D7)
what are you trying to do?

is the number with + next to it text? as number format wont show the + sign.

Hi and welcome to the forum

as suggested, please upload a sample workbook, showing the data you are working with, a few sample answers and how you arrived at that.

Having said that, you cannot to a search for a + in a string of numbers - if those numbers return a value in the cell. For instance, you cannot search for + in a cell that contains +10 - excel sees the answer of the calc, not the formula behind the answer. If on the other hand, you are searching for a + in a text string,, then search will find +, but you cannot "search" a range, only a cell, so you may have to use a helper column to search each cell individually, and then use that in your calcs.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Where would you enter the +? Would you enter it manually or would it be pulled from the data on sheets 'DAY1', 'DAY2, 'DAY3'? In which column on 'TOTAL' do you want to check the Non-tax and Tax columns for the + symbol? In which column do you want the value with the removed + symbol? There's basically no data at all in your provided example so I'm a bit confused on what you're actually looking for. You also didn't provide any expected results so there's nothing to compare against.

Ahh, ok. In that case what you're looking for is a macro.

How to use a macro:

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor
6. In Excel, press ALT+F8 to bring up the list of available macros to run
7. Double-click the desired macro (I named this one tgr)

Here is the macro code:

Please Login or Register  to view this content.

Note that you will have to save the workbook as a .xlsm file instead of .xlsx. When you close and reopen the file, you will need to enable macros in order to run it.