Extract XX% from a string

[nubranger]

Hi,

How do I extract the % (e.g., 5.81000%) from the ff strings via formula?

IRS WAT NXERO Inbis PAYER 5.81000% 20200345
IRS FET NXGSI Inbis PAYER 0.825% 20200345

Thanks

 

[RawlinsCross]

There are a few ways, some more cumbersome than others, some more robust than others. Here's one potential solution - assuming that your input is always structured as you indicate:

Private Sub Test()

Dim sString As String
Dim vParts As Variant
Dim item As Variant
Dim iIndex As Integer

'simply split the input into an array of strings (assumes consistent format)
sString = "IRS WAT NXERO Inbix Payer 5.81000% 20200345"
vParts = Split(sString, " ")

For Each item In vParts
'Gives the 0-based index location in the array of each item
iIndex = Application.Match(item, vParts, False) - 1
'Prints out the index and item
Debug.Print "Index position: " & iIndex & " String: " & item
Next item

'Returns
'Index position: 0 String: IRS
'Index position: 1 String: WAT
'Index position: 2 String: NXERO
'Index position: 3 String: Inbix
'Index position: 4 String: Payer
'Index position: 5 String: 5.81000%
'Index position: 6 String: 20200345

End Sub

 

[NdNoviceHlp]

If your data is always similar, you can put this in module code...

Public Function SplitPercent(InputStr As String) As String
Dim Temp As Variant
Temp = Split(InputStr, " ")
SplitPercent = Temp(5)
End Function

So if your data was in A1, you would enter this as sheet formula...

= SplitPercent(A1)

HTH. Dave

 

[Fluff]

If it's always the value after "Payer", try
=TRIM(LEFT(SUBSTITUTE(REPLACE(A12,1,SEARCH("payer",A12)+5,"")," ",REPT(" ",100)),100))

 

[nubranger]

If it's always the value after "Payer", try
=TRIM(LEFT(SUBSTITUTE(REPLACE(A12,1,SEARCH("payer",A12)+5,"")," ",REPT(" ",100)),100))

Great. This would work. I cannot use macro in my current workspace and needed formula instead of vba. thank you!

 

[RawlinsCross]

Neat formula Fluff - trying to reproduce it with the example provided. I have the search and replace functions figured out, but lose it from substitute backwards. Could you elaborate?

 

[Fluff]

Great. This would work. I cannot use macro in my current workspace and needed formula instead of vba. thank you!

You're welcome & thanks for the feedback.

@RawlinsCross
The Substitute replaces the final space in the string with 100 spaces, so that the left function gets just the text required along with some spaces, which are then removed by the trim function

 

[RawlinsCross]

ahhhh.. how interesting. You do not get rid of the sequence of numbers (i.e. 20200345). You just push them out 100 spaces. Very cool.

 

[Fluff]

You do get rid of the final string of numbers, because the Left function takes the first 100 characters (which won't include the final string) & then the Trim function removes all the extra blanks.

 

[RawlinsCross]

Yes of course the spaces are removed ultimately... I was doing it in steps and saw that formula 'pushed' out final string of numbers prior to the "Left" function. Nice basket of functions to know. Cheers.