#1




Formula Help Request
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 Last edited by OTPM; 02192013 at 11:21 AM. 
#2




Hi Tony,
Perhaps: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H2,"+1day","")," F",""),"S","")
__________________
Cheers, Paul Edstein [Fmr MS MVP  Word] 
#3




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 
#4




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 
#5




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)
__________________
Cheers, Paul Edstein [Fmr MS MVP  Word] 
#6




Quote:
Tony 
#7




Quote:
That worked perfectly. Many thanks for your help. Tony 
#8




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 
#9




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.
__________________
Cheers, Paul Edstein [Fmr MS MVP  Word] 
#10




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




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
__________________
Cheers, Paul Edstein [Fmr MS MVP  Word] 
#12




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




Help with a UDF needed
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 Tony 
#14




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
__________________
Cheers, Paul Edstein [Fmr MS MVP  Word] 
#15




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

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Formula help request  JAMS  Excel  2  04062012 10:14 PM 
Meeting Request Time Wrong  aimav  Outlook  0  03262012 08:14 AM 
Outlook Calendar request  steve42399  Outlook  2  04122011 09:35 AM 
Meeting Request  Another Organizer  coolpeter86  Outlook  0  12122010 11:58 PM 
Macro request: open on event  infotech  Outlook  0  12082010 06:40 PM 