# Replacing a word in the middle of a string of text with another

1. ## Re: Replacing a word in the middle of a string of text with another

edit: figured you might prefer to not have to list out every Col B value in this reference table but rather only those where you wish to adjust the string content - refer attached.

2. ## Re: Replacing a word in the middle of a string of text with another

Wow, thank you ever so much that is amazing

3. ## Re: Replacing a word in the middle of a string of text with another

Hello once again, every time I think I have jumped the final hurdle there seems to be another one a mile down the track.

I was wondering if the above can be developed for the following.

Please see the attached looking at Col A and Col F that I have added to what you sent originally.

So, now I have Col A with material number that aligns to Col B.
Currently, the formula generates in Col E has worked really well but does not consider if Col B for the same material is changing elsewhere in the document, now I need the formula to generate what I have in Col F.

Any assistance as always is massively appreciated, thank you for your help so far.

4. ## Re: Replacing a word in the middle of a string of text with another

is there a limit to how many unique values in Col C you could have for a given material number?
the sample data implies two however I suspect that, in reality, it could be many more than that?

if the answer to the above is, effectively, 1 to "n" then, whilst I'd have a think about a formula approach, you might want to consider using a Custom (VBA) Function as it will be more efficient.

5. ## Re: Replacing a word in the middle of a string of text with another

I do not fully understand your question I am afraid.

However, Col A could be literally any number up to 7 digits long.

Only the values in Col I actually impact Col B in any way and the list for Col C is only about 20/30 values total. The Value in Col D could be just about anything. Like Make up quantity 1 has every value from 1 - 999,999 assigned to it.

6. ## Re: Replacing a word in the middle of a string of text with another

sorry, the question was:

in your sample no Material # (Col A) appears against more than 2 unique entries in Column C -- is this realistic, or could a Material # appear with, say, 5 unique values in Col C - each of which could, in theory, require a string swap (as defined in table to right)

7. ## Re: Replacing a word in the middle of a string of text with another

Ah I follow, in my list of 1845 entries in Col A the most a unique individual number appears is 3 - every time being against a different Col C value

8. ## Re: Replacing a word in the middle of a string of text with another

See attachment,
Ctrl-F3 to see name manager
FstNum: 1st Qty pos
Blank1: 1st "blank space" after FstNum
....

9. ## Re: Replacing a word in the middle of a string of text with another

Hello, thank you so much - the above is brilliant.

I do not quite understand the name manager part.

The attached is a little hard wired for my needs though, when I change the information in Col C for example the updated description become wrong. I have highlighted numbers in Col A where I have amended the information in Col C/D or added a new lines and the description does not update accordingly. Not sure if you can advise.

I will be changing the data in Col A / B / C / D on a daily basis so needs to update as such

10. ## Re: Replacing a word in the middle of a string of text with another

Per my prior post I would be inclined to go the UDF route, but it might be that your source data is sufficiently limited that the approach offered in prior post will suffice?

Below would be one possible UDF, this should work irrespective of how your data is sorted, and/or how many variants you need to account for in a given material no.
Caveat: the below does assume that when swapping out 1+ strings within same element {e.g. elements 3 & 4} that the before/after values are like-for-like in terms of their respective length - e.g. 20&30 rather than 30&100

``Please Login or Register  to view this content.``
the above would be stored in a Module in VBE, and could then be called from your cells along lines of:

Formula:
`Please Login or Register  to view this content.`

note: I inserted a blank row 1 just to limit how many times you call the UDF -- i.e. it will only fire the first time it finds an item number, else it will use whatever result it conjured previously.

11. ## Re: Replacing a word in the middle of a string of text with another

My hero. I am trying to break your work but it is working every single time, amazing

12. ## Re: Replacing a word in the middle of a string of text with another

When I replace a 4 of 5 letter word for local brand family group (the second word in the string of text) with a 3 letter word only the first 3 letters are replaced and then the 4/5th letters are kept, i.e.

Col B
BLUE

Col D
RED

COL E

REDE

I know you said this in your original post but is there a solution to fix this issue?

I had previously thought this was a non issue because when the shorter word was being replaced by the longer word the full text would transfer over so RED replaced by BLUE would end up with BLUE. Rather than 'blu'. Is there a way to prevent RED replacing BLUE ending up with REDE

13. ## Re: Replacing a word in the middle of a string of text with another

For those elements that necessitate only 1 swap you could modify easily enough (below).

``Please Login or Register  to view this content.``

14. ## Re: Replacing a word in the middle of a string of text with another

Hello, thank you, sorry I did not have access to internet for a few days otherwise would have thanked you sooner.

Would the same be possible on make up quantity values? That is the final element to my problem now

In the attached Col E does not align with what it needs to be in Col F. Thank you in advance as always

15. ## Re: Replacing a word in the middle of a string of text with another

Revised UDF in attached - note additional column added to reference table.

Page 2 of 3 First 1 2 3 Last

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1