[karlbond88]

Hi there, I am currently working on a project where i need to write an automated process which will do the following:

Current Cell Content:

Albanian[62468]|Algerian[62469]|American[62470]

Cell Value After Processed:

Albanian
Algerian
American

The problem I have is there are over 150 values each with their own ID (contained in []) and a | after to seperate each value.

The two pieces of code i am using in my macro are the following:

Selection.Replace What:="[*]", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows

Selection.Replace What:="|", Replacement:=Chr(10), LookAt:=xlPart, _
SearchOrder:=xlByRows

The first one uses a wildcard (the astrix) to remove the ID, the second replaces the | with Char(10) which in excel is a line break.

I have tested it on small lists however on large amounts it will not work as it returns with an error message saying "Formula is too long".

I have done some research and found the SUBSTITUTE function however the astrix wildcard doesn't work and i cannot get the Char(10) to work (instead of inserting a line break is actually puts in the text)

I am all out of ideas and this has put my project to a hault.

Anyone got some words of wisdom?

[Advertisements]

No one got a solution?

[karlbond88]

No one got a solution?

[karlbond88]

Nevermind, found another solution in the end

[Vins]

Hey Karl,

I'm working on a macro to Find and replace a particular value in the active sheet. For eg. the value to be replaced is in Range (G3) and replacement value is in Range (G4).

Is there any way to incorporate this in a macro?

Thanks in advance

Regards
Vins