Software

Ask something
Question Locked

Excel formula question

0 helpful
5 replies

by jayevans_uk

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am using Excel 2007 and I want to create a formula that says - if A2 contains a number xxxxxx, I want Excel to enter a name into cell C2.

This is because I've been given a workbook that contains various sheets and a column for the name hasn't been included, so I want to insert it but not have to go through every line inserting the name - with the potential for making a mistake !

I do hope this can be done as I will want to use it each month until the spreadsheets comes in with the name column already completed.

Regards,
Julia

5 replies

by yogi_john

I didn't flinch at replicate. It made perfect sense to me. While I'm old enough to have used VisiCalc I never did.

by yogi_john

Julia,

Is the number xxxxxx linked to a name in a different part of the workbook? If so, you probably want one of the lookup functions.

With data in columns (that is, a row is a record) use vlookup (use Excel's help to determine the parameters).

You might need hlookup instead.

One caution since you mention doing this until the spreadsheets come in with the name: if the (number, name) pairing already exists somewhere it may be better to keep the formula than to hardcode the name. This way there is only one place where a change would need to be made if necessary (e.g., correcting a misspelling or someone changing his/her name).

by wwgorman

In C2 use the "If" command and this will insert the word you select in the command if true. You can leave it blank "if" is untrue, simply leave a space between the commas in the "if" command.

by steven.jones

Hi Julia,
Reading your problem had me asking a lot more questions. As others have alluded to, it makes a difference if your "contains a number xxxxxx" is truly a specific number you are looking for and want to replace with a specific name or if you are merely looking for number with the format of 'xxxxxx'.

The more information regarding your sample, the better and more clear the answers can be for your problem.

If you are looking for specific numbers to return specified names, you can maintain a separate spreadsheet with the lookup number and names, then as new work comes in, you can use that constant template as your reference source in a 'VLOOKUP'.

If you are just looking for whether the cell value represents a number, you can use the function 'ISNUMBER'.

You can also use Visual Basic code to do it but that'll take a fair bit more work. This is probably not the way you want to go without some expert assistance.

These are just a few solution options that come to mind. As you can see, depending on what your sample is the solution has a variety of options.

Cheers!
Steve

This discussion is closed to new replies.