Formula Help Request

OTPM · #1 02-19-2013, 06:30 AM

Hi Forum Members
I am looking for some help with a formula. Basically I am looking for a formula that will remove portions of a string of data that is sometimes separated by commas which I want to keep. For example:

From this:
123FS+1day,345,333SS,567SF

To this:
123,345,333,567

Any help would be appreciated.

Here is an update since I posted:

I have two formulae that do what I want but separately, however what I need is for both formulae to run in the same cell at the same time. Here are the two formulae:

=REPLACE(H2,FIND("+",H2,1),FIND(",",H2,1)-FIND("+",H2,1),"")
and
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H2,"S S",),H2,"FS"),H2,"SF"),H2,"FF")

Both work perfectly well independently but I want them both to work at the same time.

Many thanks in anticipation.

Tony

macropod · #2 02-19-2013, 04:31 PM

Hi Tony,

Perhaps: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H2,"+1day","")," F",""),"S","")

OTPM · #3 02-20-2013, 03:44 AM

Hi Paul
Many thanks for the response.
The +1day may be +10days or +3wks. So I need a formula that will find the location of a "+" or "-" sign and remove everything from there up to either a comma (but retain the comma) or the end of the string.
There will also be -10days or -3wks but I can work that out when I get the formula to work for the "+" options.
Kind regards
Tony

Kevin@Radstock · #4 02-21-2013, 04:06 AM

Hi OTPM

Assuming your data is in A1, may be:

=LEFT(A1,FIND("+",A1)-3)&","&MID(SUBSTITUTE(SUBSTITUTE(A1,"SS",""),"SF", ""),FIND(",",A1)+1,LEN(A1))

Kevin

macropod · #5 02-21-2013, 04:30 AM

Hi Tony,

Since you can have + or - and a variable string from there till the first comma, try:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H2,MID(H2,IF(ISE RROR(SEARCH("+",H2)),SEARCH("-",H2),SEARCH("+",H2)),SEARCH(",",H2)-IF(ISERROR(SEARCH("+",H2)),SEARCH("-",H2),SEARCH("+",H2))),""),"F",""),"S","")
(delete unwanted spaces inserted by the board software)

OTPM · #6 02-21-2013, 08:34 AM

Quote:

Originally Posted by Kevin@Radstock

Hi OTPM

Assuming your data is in A1, may be:

=LEFT(A1,FIND("+",A1)-3)&","&MID(SUBSTITUTE(SUBSTITUTE(A1,"SS",""),"SF", ""),FIND(",",A1)+1,LEN(A1))

Kevin

Many thanks for your response Kevin, I will try that.

Tony

OTPM · #7 02-21-2013, 08:38 AM

Quote:

Originally Posted by macropod

Hi Tony,

Since you can have + or - and a variable string from there till the first comma, try:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H2,MID(H2,IF(ISE RROR(SEARCH("+",H2)),SEARCH("-",H2),SEARCH("+",H2)),SEARCH(",",H2)-IF(ISERROR(SEARCH("+",H2)),SEARCH("-",H2),SEARCH("+",H2))),""),"F",""),"S","")
(delete unwanted spaces inserted by the board software)

Hi Paul
That worked perfectly.
Many thanks for your help.
Tony

OTPM · #8 02-25-2013, 07:48 AM

Hi Paul
I still need your help

.
If I type the data for the formula to work on the formula works fine. However if I copy the data from a "Text" file into Excel the formula does not work.
The data is pasted with a cell format as "General".
I have tried marking the column as "Text" prior to pasting the data but it still does not work.
I am getting #VALUE error which I know is conflicting data types but the data is in one cell!
Any help would be appreciated.
Thanks
Tony

macropod · #9 02-25-2013, 04:02 PM

Hi Tony,

Can you attach a workbook to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab.

OTPM · #10 02-26-2013, 12:16 AM

Hi Paul
Here is an example workbook.
When I type the data into a cell and then apply the formula it works fine!!!!
Tony

macropod · #11 02-26-2013, 01:32 AM

Hi Tony,

The formula I posted is predicated on the data containg a + or - with commas occuring after them. Some of the data in your workbook lack either or both. Hence the errors. Here's a UDF that's perhaps more flexible & robust:

Code:

Function ParseIt(StrIn As Variant) As Variant
Dim i As Long, j As Long, k As Long
Dim StrTmp As String, StrOut As String
For i = 0 To UBound(Split(StrIn, ","))
  StrTmp = Split(Split(Split(StrIn, ",")(i), "+")(0), "-")(0)
  k = Len(StrTmp)
  For j = k To 1 Step -1
    If Not IsNumeric(Mid(StrTmp, j, 1)) Then
      StrTmp = Replace(StrTmp, Mid(StrTmp, j, 1), "")
    End If
  Next
  StrOut = StrOut & StrTmp & ","
Next
ParseIt = Left(StrOut, Len(StrOut) - 1)
End Function

To use it, add the function to an ordinary code module, then input a reference to it as a formula in the desired cell. For example, in J4, use =ParseIt(I4)

OTPM · #12 02-26-2013, 01:52 AM

Hi Paul
This is excellent, many thanks for your help.
Tony

OTPM · #13 12-12-2013, 03:43 PM

Hi
I was provided with a UDF by Macropod a couple of years ago which I need modifying. Basically I need the UDF modified. The UDF basically converts values like "365SS+4wks, 175" to "365, 175". Where I need the UDF changed is allow values such as "23,456,567,666" to remain as shown. Here is a copy of the UDF:

Code:

Function ParseIt(StrIn As Variant) As Variant
Dim i As Long, j As Long, k As Long
Dim StrTmp As String, StrOut As String
For i = 0 To UBound(Split(StrIn, ","))
  StrTmp = Split(Split(Split(StrIn, ",")(i), "+")(0), "-")(0)
  k = Len(StrTmp)
  For j = k To 1 Step -1
    If Not IsNumeric(Mid(StrTmp, j, 1)) Then
      StrTmp = Replace(StrTmp, Mid(StrTmp, j, 1), "")
    End If
  Next
  StrOut = StrOut & StrTmp & ","
Next
ParseIt = Left(StrOut, Len(StrOut) - 1)
End Function

Any help appreciated.
Tony

macropod · #14 12-13-2013, 02:15 AM

Try:

Code:

Function ParseIt(StrIn As Variant) As Variant
Dim i As Long, j As Long, k As Long
Dim StrTmp As String, StrOut As String
If IsNumeric(StrIn) Then
  ParseIt = StrIn
  Exit Function
End If
For i = 0 To UBound(Split(StrIn, ","))
  StrTmp = Split(Split(Split(StrIn, ",")(i), "+")(0), "-")(0)
  k = Len(StrTmp)
  For j = k To 1 Step -1
    If Not IsNumeric(Mid(StrTmp, j, 1)) Then
      StrTmp = Replace(StrTmp, Mid(StrTmp, j, 1), "")
    End If
  Next
  StrOut = StrOut & StrTmp & ","
Next
ParseIt = Left(StrOut, Len(StrOut) - 1)
End Function

OTPM · #15 12-13-2013, 02:14 PM

Many thanks Paul. I will test it out over the weekend.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formula help request	JAMS	Excel	2	04-06-2012 10:14 PM
Meeting Request Time Wrong	aimav	Outlook	0	03-26-2012 08:14 AM
Outlook Calendar request	steve42399	Outlook	2	04-12-2011 09:35 AM
Meeting Request - Another Organizer	coolpeter86	Outlook	0	12-12-2010 11:58 PM
Macro request: open on event	infotech	Outlook	0	12-08-2010 06:40 PM