Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel: Find & Replace Macro Issue


  • Please log in to reply

#1
karlbond88

karlbond88

    Member

  • Member
  • PipPip
  • 40 posts
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? :)
  • 0

Advertisements


#2
karlbond88

karlbond88

    Member

  • Topic Starter
  • Member
  • PipPip
  • 40 posts
No one got a solution? :) :) :)
  • 0

#3
karlbond88

karlbond88

    Member

  • Topic Starter
  • Member
  • PipPip
  • 40 posts
Nevermind, found another solution in the end
  • 0

#4
Vins

Vins

    New Member

  • Member
  • Pip
  • 1 posts
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
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP