Free Microsoft Excel 2013 Quick Reference

Extracting characters before or after a specific character

Howdy All,

I have data imported from a web query, but it isn't formatted the way I need
it.

I have a part number and description combined in a cell, separated by --.

How do I extract the data before the -- and then the data following the -- ?

Thanks,
Brian


I am back with my "If" statement problems! I need my if statement to return a certain string of text based on before or after a certain date. I have tried putting the

I have a field that is not a fixed length. It contains characters before and after a colon (":") (including spaces). I need to eliminate all of the characters after the ":" and just return the characters and spaces before the ":". The number of characters before the ":" varies (or I would just use Left()). Would someone please help me with a formula to return only the characters before the ":"?
Thank you.

I'm compiling a cheat sheet of formulas for my office workers to use. When I get to the Text functions, I can find examples of how to do just about everything except extract text before/after a particular string. I've found a few formulas for doing it after a symbol, but they don't work when it comes to a string.

For example, say I have 15 paragraphs that all begin with "Yo mamma's so ugly that..." and I want to extract whatever follows after "that". Or, say I have 1000 lines of text formatted
 and I want to extract everything after the 4th character string (HERD in this example), or before the 4th string.  Is there
a way to do that in a formula that can adapted to any situation?    

Your help in augmenting my knowledge is already greatly appreciated.

Thanks,
John

Hi guys,

What is the best way to highlight (or even color) all the cells in a
worksheet (or even in a workbook) containing a specific character? For
exemple, I would like to make visually distinctive all the cells containing
somewhere a "?" character (or "mmm" string, or "!" mark, or "=" sign).

Are you aware of any special easy macro doing (and also undoing) this stuff?
Or do you have idea how to write a special formula to put in "conditionnal
formatting" part?

Thanks a lot for your input,
Mark

Roger,
One way:-

Sub Test()
Dim intA As Integer, strB As String
Dim strC As String, Var
Var = ActiveCell.Value ' (or whatever)
intA = Left(Var, 2)
strB = Mid(Var, 3, 1)
strC = Right(Var, 1)
End Sub

This only works if the format of the target is always the same.

regards'
Don
--

"Roger" > wrote in message
om...
> Hi All,
>
> I have an issue with how to split cell content without using a
> specific character/sign.
>
> Eg
> The content of a cell is 13GR.
>
> The searched result after the splitting of the cell content shall be:
> intA= 13
> strB = G
> strC = R
>
> How should the code be written to solve the issue?
>
> I would be very grateful for help.
> Roger

Hi guys,

What is the best way to highlight (or even color) all the cells in a
worksheet (or even in a workbook) containing a specific character? For
exemple, I would like to make visually distinctive all the cells containing
somewhere a "?" character (or "mmm" string, or "!" mark, or "=" sign).

Are you aware of any special easy macro doing (and also undoing) this stuff?
Or do you have idea how to write a special formula to put in "conditionnal
formatting" part?

Thanks a lot for your input,
Mark

Hi,

How can I quickly code Excel Macro to extract characaters (as string) before and after
a unique symbol (such as "-" / hyphen)?

For example if I had a string of arbitrary lengths before and after the middle symbol

f454d-6sd4f6sd4f
df54ds5-f54d4b
sdf46d-1g5f4b

I set part1 and part2 to strings.

What code can I do to assign part1 to be everything BEFORE the HYPHEN
and part2 to be everything AFTER the hypen??

I am trying to count the number of times a specific character appears
in a cell (or range of cells). Anyone know how to do this?

Example: The cell contains "Fruit/Meat/Fish" and I would like a
formula counting the number of times "/" appears (returning the value
2).

Brgds,
Harald

Hi,

Can anybody help me how to get this done. I want excel to display
characters when after a specified point. Like I have a huge list of
email addresses and I want to display domain names on the next cell.
Excel should display the characters present after @ symbol.

Hi, I was hoping someone could help me with this. How can I delete everything after a certain character (for this example: - ), no matter how much there is after - in each cell.

Or, is there a way that i can move everything after the "-" to an new cell?

Excel 2010

Thank you.
TTG

Thank you jeffreybrown It worked perfect!

I want to get the character set value of a specific character within a alphabetical value in a cell.

Is there a quick line of code or formula to do this?

Thanks

Hi,
I want to know how do I check if the text string starts with a specific
character in if statement.

I used ...
=IF(ISNUMBER(FIND("v",'A2)),"OK","Not OK")

But, the result for this argument returns the text string which includes "v"
anywhere in the text string.
I just want to the text string which begins with "v".

How can I make it?

How can I get excel to apply values in a cell using an IF function before or
after dates displayed in another cell? It will allow me to use a
datevalue(...)= but not datevalue(...)> or datevalue(...)

As the topic states, I am looking to change a specific character in a cell using VBA code. The twist is how to change the character one at a time.
For example: Changing 3's to 8's one at a time for a cell, where I would want to return something like change3once = 28253.213, change3twice = 28258.213, etc

I have been searching online, but had been unsuccessful in finding a solution, and was hoping someone on here may have a good solution

How would I set Conditional Formatting (in Excel 2007) to identify a cell that contains a specific character (for example "*")?

Hello
I need to make a list of Part Numbers in quotation marks. If a number of digits of a P/N is less than 13 a number of space characters has to be added to make the string 13 characters long.

We have example P/N:
1234567890123
12345678
123456

should become:
"1234567890123(no extra space characters should be filled-13chars)"
"12345678(5 extra space characters here)"
"123456(7 extra space characters)"

Is there a function that inputs a cerain number of a specific character ("space" in this example)?
Thank you!

This one should be easy for you all.

I have todays date in cell A2

I have a range of dates in a row 4 and numbers below each date in row 6.

What i want is to Sum the numbers below the dates in row 6 if the date in row 4 is with in 90 days before or after the date in cell A2.

Example

todays date is 10-29-09

05-15-09 , 08-15-09 , 09-15-09 , 12-15-09 , 04-15-10
1 , 2 , 1 , 1 , 1

The answer i should get is 4 since only 3 dates fall 90 days before or after todays date.

Thoughts?

How can I get excel to apply values in a cell using an IF function before or
after dates displayed in another cell? It will allow me to use a
datevalue(...)= but not datevalue(...)> or datevalue(...)<.

How do I hide/remove a specific character from a cell?

I have a cell with "G4N" entered in it. I have another cell that I want to reference the "G4N" cell and display just the "G", a third cell to display the "4", and a fourth cell to display the "N".

Any help would be appreciated.

Thanks
dlb

Hi,
I want to know how do I check if the text string starts with a specific
character in if statement.

I used ...
=IF(ISNUMBER(FIND("v",'A2)),"OK","Not OK")

But, the result for this argument returns the text string which includes "v"
anywhere in the text string.
I just want to the text string which begins with "v".

How can I make it?

Hello,

How do i add text to every cell in a column before or after the text already in the cell? I once had a macro but i forgot how to do it. Any help is much appreciated.

Hi,

Someone please help me with a code snippet where just before or after the ActiveWorkbook.close, I want to start an .mdb file (in it I start up as Autoexec).

I have to do this because it is part of a series of the automation task for the different daily reportings.

I am using Excel and Access 2002 in Windows XP.
Any help is much appreciated. Thanks.

How do I create a formula in Excel that will countif or sum if before or
after a specific date? Whenever I put the actual date in the formula it
works, but when I reference to the cell the date is in it returns 0.

Hello All,

I have Case or Incident Numbers in Column A. Column B has log created dates. Column C has log closed dates.

End result will be the number of cases that do not have repeat logs.

I need to find duplicate case numbers and then compare the logs. If there is not a duplicate case number, Column D needs to say No (it says original in my sample for visual purposes). If there is a duplicate case number, then it needs to look at the first log closed date and look at the duplicate log and decide if it was created before or after that activity closed. If it is before it does not meet my criteria, the log must be closed. If it is after, a simple yes or repeat would work.

I put an array in column E to find the max date for the case number but i think VBA is going to be the way to get this done. In my sample I have included 21 lines however my data contains over 600,000 rows of data.

I thought the max column would help me but I need it to look from the row down.

Case 1

1st Log Created 8/01/2009 08:00 AM Log Closed 8/01/2009 10:00 AM
2nd Log Created 8/01/2009 11:00 AM Log Closed 8/10/2009 10:00 AM
3rd Log Created 8/03/2009 08:00 AM Log Closed 8/05/2009 10:00 AM
4th Log Created 8/12/2009 08:00 AM Log Closed 8/15/2009 10:00 AM

So, first log is the original. 2nd log would be a repeat. 3rd log cannot be a repeat because the 2nd is still open. 4th log would be a repeat because the second log closed.

Hope that is clear enough. Any help appreciated. Thanks