Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-19-2013, 06:30 AM
OTPM OTPM is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Expert
Formula Help Request
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 02-19-2013, 04:31 PM
macropod's Avatar
macropod macropod is offline Formula Help Request Windows 7 64bit Formula Help Request Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,563
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Hi Tony,

Perhaps: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H2,"+1day","")," F",""),"S","")
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-20-2013, 03:44 AM
OTPM OTPM is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Expert
Formula Help Request
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 02-21-2013, 04:06 AM
Kevin@Radstock Kevin@Radstock is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2012
Posts: 91
Kevin@Radstock is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 02-21-2013, 04:30 AM
macropod's Avatar
macropod macropod is offline Formula Help Request Windows 7 64bit Formula Help Request Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,563
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #6  
Old 02-21-2013, 08:34 AM
OTPM OTPM is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Expert
Formula Help Request
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Quote:
Originally Posted by Kevin@Radstock View Post
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
Reply With Quote
  #7  
Old 02-21-2013, 08:38 AM
OTPM OTPM is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Expert
Formula Help Request
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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
Reply With Quote
  #8  
Old 02-25-2013, 07:48 AM
OTPM OTPM is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Expert
Formula Help Request
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 02-25-2013, 04:02 PM
macropod's Avatar
macropod macropod is offline Formula Help Request Windows 7 64bit Formula Help Request Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,563
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #10  
Old 02-26-2013, 12:16 AM
OTPM OTPM is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Expert
Formula Help Request
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Paul
Here is an example workbook.
When I type the data into a cell and then apply the formula it works fine!!!!
Tony
Attached Files
File Type: xlsx Test ORH.pdf.xlsx (245.7 KB, 8 views)
Reply With Quote
  #11  
Old 02-26-2013, 01:32 AM
macropod's Avatar
macropod macropod is offline Formula Help Request Windows 7 64bit Formula Help Request Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,563
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 02-26-2013, 01:52 AM
OTPM OTPM is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Expert
Formula Help Request
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Paul
This is excellent, many thanks for your help.
Tony
Reply With Quote
  #13  
Old 12-12-2013, 03:43 PM
OTPM OTPM is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Expert
Formula Help Request
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default 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
Any help appreciated.
Tony
Reply With Quote
  #14  
Old 12-13-2013, 02:15 AM
macropod's Avatar
macropod macropod is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,563
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #15  
Old 12-13-2013, 02:14 PM
OTPM OTPM is offline Formula Help Request Windows 7 32bit Formula Help Request Office 2010 32bit
Expert
Formula Help Request
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Help Request 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:07 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft