Please Modify this VBA for New Requirement Or Provide a New One

Hi Good Evening from Sri Lanka,

Please help if possible.

The below WAS my requirement and the solution to it is Below,

Data I had :-

Cell A1 = Phone: 81-74568943 Fax: -
Cell A2 = Phone: 51-83-9498756, 5583772359 Fax: 61-88-4932516
Cell A3 = Phone: 51-6785239556, Fax: 31-13-45225335
Cell A4 = Phone: 51-6785239556, Fax: 31-13-45225335, Mobile: -

Outcome :-

B1 = Phone
C1 = 81-74568943
D1 = Fax
E1 = - (Or N/A - Not the N/A ErrorMessage or a value! errormessage, But just the letters : N/A)
F1 = Mobile
G1 = - (Or N/A - Not the N/A ErrorMessage or a value! errormessage, But just the letters : N/A)

The Formula to get it done is below,


http://www.vbaexpress.com/forum/show...078#post360078


How should I remodel the above in

My Issue;

A1=Contact Person: James Anderson Designation: Proprietor
A2=Contact Person: Gordan Designation: Seller
A3=Contact Person: David Cameron Miller Designation: Vice President

Solution I require:

to
B1=Contact Person
C1=James Anderson
D1=Designation
E1=Proprietor

B2=Contact Person
C2=Gordan
D2=Designation
E2=Seller

B3=Contact Person
C3=David Cameron Miller
D3=Designation
E3=Vice President

?

Please Help !!!

You are starting a lot of threads on what is basically the same question.

Try this

Test data on sheet 2 of attachment

Code:

---

Function BreakOut2(s As String) As Variant
    Dim v As Variant
    Dim aOut As Variant
    Dim i As Long
    Dim s1 As String
   
    Application.Volatile
       
    s1 = s
   
    s1 = Replace(s1, " Designation", ": Designation")
   
    v = Split(s1, ":")
   
    BreakOut2 = v
End Function

---

Also cross posted here

https://www.mrexcel.com/forum/excel-...ata-range.html

Please provide your samples in an attached workbook. It saves the responders from replicating what you have already created.

01. First and Foremost I should Thank all of you for your continues Support given here.

02. Then I apologize if I caused any inconvenience to you guys the Moderators and Admins Here.

03. I didn't know that Mr excel and you guys are linked to each other. Faster responses are found in both places, that is why I post same questions in both the places.

04. Honestly I do not understand how the UDF's or Macro's work. Formulas I normally understand because we directly work it out in the Sheet itself.

05. I press the Quote option and it shows the entire comment of the next user, Guess that seems to create an issue here as well, therefore will make sure to delete parts which are irrelevant.

06. My Questions have slight variances if you see them. In your eyes it may look the same because you guys know the to decode issues.
At times I just need to remove a word like the UDF =RW(Cell) example you had mentioned.
At some other time I need a specific outcome after removing the Word, that has been the difference. I understand from "RW" I could remove one or few words at a time, but the issue starts when Blanks also start to popup... and also it is a difficult process at my end to have so many colomns. That is why I ask for a new UDF.

07. MdMack, sure will try to do that :)


Sorry again and Thank you.

Thank you for your reply & support Chief, and it has been a great amount of help from you as well with my current issue. The Names of the Individuals seems to have space and I managed to solve that Through TRIM function..

Thank you very much again...

Quote:

---

03. I didn't know that Mr excel and you guys are linked to each other.

---

They are not linked but many responders reply in multiple forums - have a read of this

Quote:

---

04. Honestly I do not understand how the UDF's or Macro's work

---

Google "VBA for beginners" there are many useful resources out there

Quote:

---

05. I press the Quote option and it shows the entire comment

---

Copy and paste the content; use the quote button to add the formatting

Quote:

---

03. I didn't know that Mr excel and you guys are linked to each other. Faster responses are found in both places, that is why I post same questions in both the places.

---

1 - Not linked, but we think it's polite to let us know that other forums might also be working on the question.

This is in our FAQ and explains a little more. There's a nice link at the bottom for more reasons

http://www.vbaexpress.com/forum/faq...._new_faq_item3



2 - Someone mentioned your 3 very similar requests to parse out a string.

If you had put the entire requirement (all three types) in one question and it would likely be possible to generate a single (but smarter) UDF to handle the complete requirement.

(I'm waiting for a 4th :) )

Quote:

---

Originally Posted by Paul_Hossler

(I'm waiting for a 4th :) )

---

:D

Honestly and Truly I apologize guys. If not for the support you've given me it would have been very difficult for me.... Thanks again. Highly appreciated...