String Manipulation or VB?

[JayR]

I am extracting data from sharepoint and need a formula to change this string:


Text string;#40;#Some more text;#284;#and some more;#1003;#and some more again;#410


to this sting


Text string

Some more text

and some more

and some more again


the numbers between ;# can vary from 1 digit to 4 digits and the number of fields is a variable. I would like a formula but could use VB/new function but not sure of the code. Can someone help me?


Thanks in advance

 

[SirJB7]

Hi, JayR!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


If it's suitable for you replacing the semicolons for line feeds leaving output like this:

"Text string

#40

#Some more text

#284

#and some more

#1003

#and some more again

#410"

you could use this formula:

=SUSTITUIR(A1;";";CARACTER(10)) -----> in english: =SUBSTITUTE(A1,";",CHAR(10))


Regards!

 

[JayR]

Thanks for the reply SirJB7...

The numbers and # need to come out of the string.


This is what I have come up with so far... I don't like this approach but cannot think of how to solve.


Copy this formula into column E with the data in column C


=IF(ISERR(SUBSTITUTE(D6,MID(D6,FIND(";#",D6),FIND(";#",D6,FIND(";#",D6)+1)-FIND(";#",D6)+2),CHAR(10))),IF(ISERR(LEFT(D6,FIND(";#",D6)-1)),D6,LEFT(D6,FIND(";#",D6)-1)),SUBSTITUTE(D6,MID(D6,FIND(";#",D6),FIND(";#",D6,FIND(";#",D6)+1)-FIND(";#",D6)+2),CHAR(10)))

Then I run the following macro:

[pre]

Sub Macro3()
Range("C:C").Select
Selection.Copy
Range("D:D").Select
ActiveSheet.Paste

For x = 1 To 30
Range("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Range("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next x
End Sub

[/pre]

 

[SirJB7]

Hi, JayR!


Copy this code and paste it into a VBA module, then call it in your spreadsheet as:

=StringRemovalNotSoSimple(A1)


Change A1 to your source cell and format output properly (wrap text).


-----

[pre]

Option Explicit

Function StringRemovalNotSoSimple(psText As String) As String
' constants
Const ksTarget = ";#"
' declarations
Dim I As Integer, J As Integer, K As Integer, A As String, B As String, C As String
' start
I = 1
A = ""
B = ksTarget & psText & ksTarget
' process
Do Until I + Len(ksTarget) - 1 >= Len(B)
J = InStr(I + 1, B, ksTarget)
C = Mid$(B, I + Len(ksTarget), J - I - Len(ksTarget))
Debug.Print I, J, C,
If Val(C) = 0 Then
If Len(A) > 0 Then A = A & vbCrLf
A = A & C
Debug.Print A
I = J
Else
I = J
Debug.Print
End If
Loop
' end
StringRemovalNotSoSimple = A
End Function

[/pre]
-----


Just advise if any issue.


Regards!

 

[JayR]

This does exactly what I was looking for. I will be learning a lot from your code. Thank you SirJB7.


BTW

I also learned from a colleague that I could have used "find and replace" twice instead of using a function. I didn't know that I could use wild cards in a search.


Find: /#*#/

Replace: <ALT 010>


2nd find is required to get rid of the last /#

Find: /#*

Replace: <Blank>

 

[SirJB7]

Hi, JayR!


Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.


Regards!


PS: It's another alternative thanks to Excel flexibility to allow doing the same thing in different ways. You'd try to build your own code or adapt mine to use F&R; if you do please post the solution here to share it with the community. Thank you.

 

[JayR]

I have another question with the code that was provided...

I am wanting to use this function in a table and instead of using:


StringRemovalNotSoSimple(C1)


I would like to use the Table headers


StringRemovalNotSoSimple([Solution])


I am receiving "#VALUE!" error by using this method...


Can this be easily fixed?

 

[NARAYANK991]

Hi Jay ,


You need to use a fully qualified reference , like this :


=StringRemovalNotSoSimple(Table1[[#This Row],[Solution]])


Narayan

 

[JayR]

I learn something new everytime I read a response from you "Ninjas". Thank You Narayank991.


That answers my 2nd question. Enjoy your day