#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; 02-19-2013 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 | 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 |